We have a linked server setup on our production, test, and development Windows SQL Server 2012 machines that all connect to our production Oracle 19c database.

All three linked servers in each environment are named Prod_LS and connect via the same username/password of Ora_LS_User.

From within an Oracle privileged account, I granted select access to an additional table for the Ora_LS_User.

From SQL Server test machine when I execute:

SELECT * FROM OPENQUERY(Prod_Ls, 'select * from orders') 

I get following message only in SQL Server test environment (prod and dev work):

An error occurred while preparing the query "select * from orders" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "Prod_Ls".

I connect to Oracle database as Ora_Ls_User and can select * from orders.

Any suggestions? I have dropped and re-created the linked server in test and the orders table still does not show in the linked server list in SQL Server test environment?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.