TRY CATCH with Linked Server in SQL Server 2005 Not Working
- by Robert Stanley
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!
