I was experiencing this issue and sometimes the database would go into recovery - and rarely would take the server offline. Error returned from SQL Server:
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".
Here are my suggestions if you experience the error above.
- First - 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.
- See if you are able to execute a very simple query to a small table that you should be able to access using the same linked server reporting the problem.
Example:
SELECT * FROM OPENQUERY
(
YOURLINKEDSERVER,
N'
SELECT *
FROM SOMETABLE
'
);
If you don't experience an issue with this query then I suggest reviewing the syntax inside your OPENQUERY command text. See if you have any comments in the code, uncommon commands, or special formatting. The OLE DB prodvider doesn't always handle these items very well.
My issue was the comments in the OPENQUERY command text. Once I removed the comments I was able to execute without errors.
I hope this provides some assitance.
