Thursday, February 16, 2012

Cannot get linked server working to Oracle 10.1 database

Hi -

I am having an issue trying to get a linked server to work from SQL Server 2000 SP4 to an Oracle 10.1 database. This server runs Windows 2003 Server.

I had first installed the Oracle client 10.2 on the server and was able to access the Oracle db using the client.

However - when I set up the linked server on SQL Server - and click the "Tables" underneath the linked server - I get the following error.

OLE DB Provider reported an error.

OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned]

I uninstalled the 10.2 client and put the Oracle 9i client on the server (on a suggestion from a SQL Server DBA to put an earlier client on the server) - still able to connect using the Oracle client - but not via SQL Server.

I have checked to make sure the MSDAORA.dll is registered. I have also checked that the account that SQL Server starts under has proper permissions to the Oracle folders (that login has admin permissions on the server).

I have named the Linked Server the same name as the service name in tsanames.ora.

I'm at a loss - any ideas? Help - i'm tearing my hair out here.
I figured out the issue - I came across this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1190237&SiteID=1

The registry settings detailed in this post solved it for me - specifically:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll.


No comments:

Post a Comment