August 5, 2008 at 3:41 pm
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?
August 5, 2008 at 9:54 pm
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.
August 6, 2008 at 8:56 am
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.
August 13, 2008 at 6:39 am
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
March 6, 2009 at 3:11 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply