TRY CATCH with Linked Server in SQL Server 2005 Not Working

Question

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!

Answer 1

See Handling Errors in Server-to-Server Remote Stored Procedures:

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.