Cannot query ORACLE database through linked server

Question

I am facing the issue while querying the ORACLE database from linked server.

The error message which I get is:

An error occurred while preparing the query

SELECT COUNT(*) FROM OracleDBInstance.testTable for execution against OLE DB provider ADsDSOObject for linked server lnk_server.

My query is:

select * from openquery(lnk_server,'SELECT COUNT(*) FROM OracleDBInstance.testTable')

I have searched a lot, but everyone is providing solution for Active Directory query.

Can any one provide the correct solution?

Answer 1

I had the same problem

In some cases when using the SSMS interface to create a linked server using Oracle Provider for OLE DB, in the background it instead uses ADsDSOObject.

To resolve the issue I followed these steps:

1. Create the linked server using Oracle Provider for OLE DB
2. Right click the new linked server, select Script Linked Server As -> CREATE To -> New Query Editor Window
3. Delete the existing linked server
4. Change the generated query. change @provider from ADsDSOObject to N'OraOLEDB.Oracle
5. Run the query

This worked for me to use OraOLEDB.Oracle rather than ADsDsOObject.