Struggling to link an oracle server

i am running a web site with full login etc powered using my sql server
we use an internal data system running on oracle... i need the data for the web from that oracle db

to run reports in crystal staff use 'oracle net manager' for their connection which lets them run their full info and get their reports.

i need to get this type of detail to correspond with my sql users etc so need to link the server in some way

when i go to linked servers NEW not sure what to use
should i use 'microsoft OLE DB provider for oracle' or 'oracle provider for ole db'

on both of these options i then get asked for
Product Name:
Data source:
Provider String:

not sure what i need for these, i have the db name, server name, server IP, username and password for the oracle db, just not sure what order and if i have all information.

also i read somewhere about having oracle installed on my sql server... if orable net manager can connect, should this not mean i can get sql to connect, or somehow use this oracle net manager to connect the linked server.

I have tried a connection specifying Product Name as the db name and the data source as the server name adding in a security user with the usernme and password.
When simply trying to view tables i get:
error 7399 OLE DB provider 'OraOLEDB.oracle' reported an error
OLE DB error trace [OLE/DB provider IDBInitialize: initialize returned 0x80004005:]

not sure where i'm going wrongor if i've just entered the wrong detail in each section??
bhendrickAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ThejakaConnect With a Mentor Commented:
Try:

OLE DB provider: Microsoft OLE DB Provider for Oracle
Product Name: {Optional/Any}
Provider Name: MSDAORA
Datasource: SQL*Net alias for Oracle database


Or:

OLE DB provider: Oracle Provider for OLE DB
Product Name: {Optional/Any}
Provider Name: OraOLEDB.Oracle
Datasource: Alias for Oracle Database


Links:

http://technet.microsoft.com/en-us/library/ms190479.aspx
http://support.microsoft.com/kb/280106
http://www.sqlmag.com/Articles/ArticleID/49687/49687.html?Ad=1
0
 
BrandonGalderisiCommented:
Have you installed the data driver for oracle 9i/10g?
0
 
bhendrickAuthor Commented:
not sure, where would i 1. find out if i have the driver installed, and 2. find the driver??
sorry, limited knowledge on oracle
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
BrandonGalderisiCommented:
ME too.  I struggled with this when I did it once.  Let me find it.
0
 
BrandonGalderisiCommented:
Are you running 9i? 10g? other?
0
 
bhendrickAuthor Commented:
Its 10G the oracle client is running on the sql server and can connect directly through the oracle application as the listener is running fine.
0
 
BrandonGalderisiCommented:
What syntax did you use to add the linked server?
EXEC sp_addlinkedserver
    @server = 'LONDON Mktg',
    @srvproduct = 'Oracle',
    @provider = 'MSDAORA',
    @datasrc = 'MyServer'
GO

Open in new window

0
 
bhendrickAuthor Commented:
gave good info that gave a more confident approach to the situation...

link fixed, thanyou
0
All Courses

From novice to tech pro — start learning today.