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].R aiseError
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!
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].R
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or just alter the procedure [Server].[Catalog].[dbo].R aiseError to return 1 as output in case of any failures and then validate the output here..
ASKER
@rrjegan17: True, I am already doing that in the real world implementation... I just wanted to be able to log the exact error.
ASKER
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!