This post briefly discusses an issue experienced when executing a query against a linked server to Oracle from SQL Server. The error returned from SQL Server is shown below.
The OLE DB provider “OraOLEDB.Oracle” for linked server “SQXYZ” reported an error. The provider reported an unexpected catastrophic failure. An error occurred while submitting the query text to OLE DB provider “OraOLEDB.Oracle” for linked server “SQXYZ”.
This issue was very frustrating and perplexing. In my case, the linked server query was part of an INSERT command called from within a stored procedure. Some SQL was built dynamically, stored in a variable, and executed using the variable. Some SQL was built explicitly. Both of these cases had the same results in terms of receiving the error. This issue caused other databases residing on the instance to go into recovery – once this even took down the instance.
Here are my suggestions if you experience the error mentioned above; follow them in order.
- If the command exists within a stored procedure, be sure the procedure has “WITH EXECUTE AS CALLER” at the beginning of the procedure declaration.
- Test the connection for the linked server. You can do this using sp_testlinkedserver [ @servername ] or by right clicking on the linked server in Server Object > Linked Servers and choosing Test Connection. If this fails then ensure the remote sever is online and that the credentials supplied are correct.
- Using the linked server, try executing a simple query against a single table having few rows. (Since any user can query the DUAL table, try to query a table outside of the SYSTEM schema.)
- Test the query being used against the linked server by querying the Oracle database directly. You can use SQL*Plus, Toad, or some other query tool. If possible, log into the Oracle database using the same credentials supplied for the linked server. Make sure the query executes without errors.
- Review the syntax inside the OPENQUERY command text for SQL comments, special formatting, or uncommon commands. The Oracle OLE DB provider has some limitations in terms of syntax. Remove any SQL comments and special formatting.
What resolved my issue was removing the comments inside the OPENQUERY command text.
Example for step 2:
SELECT * FROM OPENQUERY
I originally posted this article on CodeProject.com.