Catching Errors Through A Linked Server With Severity < 20

I've got a similar problem to this question: TRY CATCH with Linked Server in SQL Server 2005 Not Working I'm running this try catch: Declare @command nvarchar(100) SET @com

Solution 1:

I found out how to get around this by passing the try catch to the linked server and getting the error back using the OUTPUT parameter. For example:

SET @command = '
BEGIN TRY
    exec (''select * from xxx'') 
    SELECT @resultOUT = @@ERROR
END TRY
BEGIN CATCH
    SELECT @resultOUT = @@ERROR
END CATCH'
SET @ParmDefinition = N'@resultOUT nvarchar(5) OUTPUT'
exec my_linked_server.sp_executesql 
    @command, 
    @ParmDefinition, 
    @resultOUT=@result OUTPUT