Linked Server to Oracle through Microsoft OLEDB provider

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Working with Oracle » Linked Server to Oracle through Microsoft...

Linked Server to Oracle through Microsoft OLEDB provider

Rate Topic

Display Mode

Topic Options

Author

Message

praveen voleti

Posted Tuesday, August 05, 2008 3:41 PM

SSC 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

happycat59

Posted Tuesday, August 05, 2008 9:54 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:20 PM
Points: 2,489, Visits: 2,092

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

praveen voleti

Posted Wednesday, August 06, 2008 8:56 AM

SSC 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

Sakthivel Chidambaram

Posted Wednesday, August 13, 2008 6:39 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:40 PM
Points: 309, Visits: 807

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

victor.olufowobi

Posted Friday, March 06, 2009 3:11 AM

SSC-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 »

Permissions