TRY CATCH with Linked Server in SQL Server 2005 Not Working

Hello, I am trying to catch sql error raised when I execute a stored procedure on a linked server. Both Servers are running SQL Server 2005.

To prove the issue I have created a stored procedure on the linked server called Raise error that executes the following code:

    RAISERROR('An error', 16, 1);

If I execute the stored procedure directly on the linked server using the following code I get a result set with 'An error', '16' as expected (ie the code enters the catch block):

    BEGIN TRY
        EXEC [dbo].[RaiseError];
    END TRY
    BEGIN CATCH
          DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
          SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
          SELECT @ErrMsg, @ErrSeverity;
    END CATCH

If I run the following code on my local server to execute the stored procedure on the linked server then SSMS gives me the message 'Query completed with errors', .Msg 50000, Level 16, State 1, Procedure RaiseError, Line 13
An error'

    BEGIN TRY
          EXEC [Server].[Catalog].[dbo].RaiseError
    END TRY
    BEGIN CATCH
          DECLARE @SPErrMsg nvarchar(4000), @SPErrSeverity int;
          SELECT @SPErrMsg = ERROR_MESSAGE(), @SPErrSeverity = ERROR_SEVERITY();
          SELECT @SPErrMsg, @SPErrSeverity;
    END CATCH

My Question is can I catch the error generated when the Linked server stored procedure executes?

Thanks in advance!
LVL 1
peddleAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
In short, no, most of the time.  Read below.

Below is an excerpt from MSDN BOL:

RAISERROR and TRY…CATCH

Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. If a remote stored procedure calls RAISERROR with severity less than 20 and the remote stored procedure is scoped within a TRY block on the local server, RAISERROR does not cause control to pass to the CATCH block of the TRY…CATCH construct. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.


http://msdn.microsoft.com/en-us/library/ms191515.aspx
0
 
peddleAuthor Commented:
Thanks for the quick and accurate reply.

The only problem is you need to be in the sysadmin role or users with ALTER TRACE permission, and use WITH LOG to raise an error with a severity of 20 through 25 (MSDN http://msdn.microsoft.com/en-us/library/ms178592.aspx states 19 through 25 although a quick test proved otherwise? ).

Guess I'm going to have to see what the linked servers DBA is going to let me get away with!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Or just alter the procedure [Server].[Catalog].[dbo].RaiseError to return 1 as output in case of any failures and then validate the output here..
0
 
peddleAuthor Commented:
@rrjegan17: True, I am already doing that in the real world implementation... I just wanted to be able to log the exact error.
0
All Courses

From novice to tech pro — start learning today.