Link to home
Start Free TrialLog in
Avatar of peddle
peddle

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peddle
peddle

ASKER

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!
Avatar of Raja Jegan R
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..
Avatar of peddle

ASKER

@rrjegan17: True, I am already doing that in the real world implementation... I just wanted to be able to log the exact error.