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?

Why you want to use "Microsoft OLE DB Provider for Microsoft Active Directory Service" provider to query Oracle? You have to install proper OLEDB or ODBC driver for Oracle. — EKOlog
– EKOlog, Commented Jun 12, 2015 at 6:57

Char2d2 · Accepted Answer · 2020-01-29 19:30:42Z

0

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:

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

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

answered Jan 29, 2020 at 19:30

Char2d2

814 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot query ORACLE database through linked server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related