Click here to Skip to main content
11,490,973 members (72,244 online)
Click here to Skip to main content

SQL Server - Linked Server Query - Unexpected Catastorphic Failure

, 19 Jun 2014 CPOL 4.2K 5
Rate this:
Please Sign up or sign in to vote.
This briefly goes over an issue experienced when executing a query against a linked server to Oracle from SQL Server.
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.
  1. 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.
     
  2. 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

TheSqlGuy
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Lydia Gabriella16-Jul-14 11:54
memberLydia Gabriella16-Jul-14 11:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 19 Jun 2014
Article Copyright 2014 by TheSqlGuy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid