Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server to Oracle through Microsoft OLEDB provider Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 3:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 6:06 AM
Points: 33, Visits: 177
Hi All,

I have created a linked server to Oracle database. I have used the below command to create the linked server:

sp_Addlinkedserver 'ORACserv','Oracle','MSDAORA','XYZ'

where XYZ is the Database name having associated TNS entries. Oracle client tools 9.2 are installed in the system.

After the linked server connection is created, I have specified the login credentials with which to connect. I am receiving the below error while querying the data:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Here are the things that I have tried out till now:

Connect through SQL plus locally to the oracle server with the credentials given earlier. The connection succeeded and I am able to query the data.

Created one more linked server using Oracle OLEDB provider and the same credentials. The connection is working fine.

Checked the registry entries for ORACLE_HOME as specified in other forums. The directory that is being pointed is correct.

Tried both the distributed query like - select * from ORACSERV.XYZ.SCHEMANAME.TABLENAME and OPENQUERY statement. Both are failing with the above error.

I am not sure why I am receiving this error? The credentials seems to be correct and the Oracle server and SQL are also working fine.

Could someone please help in this regard?

Post #547150
Posted Tuesday, August 5, 2008 9:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:26 PM
Points: 2,926, Visits: 2,529
Are the relevant Oracle tools (SQL Net is the most important) installed on the server ?

When you say that you can connect using SQL Plus, is this from your workstation or from the (SQL) Server ? When you attempt to use the linked server, it is from the server not your workstation.



Post #547238
Posted Wednesday, August 6, 2008 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 6:06 AM
Points: 33, Visits: 177
Thanks for the reply.
The oracle client tools have been installed on the server only from where I am attempting linked server connection.
The oracle OLEDB provider is working fine but for the microsoft oledb provider for oracle, I am receiving the above given error.

Post #547600
Posted Wednesday, August 13, 2008 6:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 27, 2014 11:17 PM
Points: 309, Visits: 814
We had similair issue.. I had call with Microsoft and they are saying that their driver does'nt support any oracle database greater than 8i. Even for us the linked server was working till last week with MSDAORA but this week it throwed this same error and then later we created the linked server through oracle driver and it was working so we left the issue. Incase you find any fix, Please let this forum know...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Post #551822
Posted Friday, March 6, 2009 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 21, 2012 3:07 AM
Points: 107, Visits: 92
Edit your
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI keys to contain the following:

a) "OracleOciLib"="oci.dll"
b) "OracleXaLib"="oraclient9.dll".
c) "OracleSqlLib"="orasql9.dll"

Then reboot your server. (I'm assuming the Oracle linked server is Oracle 9i)

Victor
Post #670035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse