I have created a linked server to Oracle database. I have used the below command to create the linked server:
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?