Catching Errors through a linked server with severity < 20

Question

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 @command = 'SELECT column FROM table'
    BEGIN TRY
        BEGIN TRY
            exec ' + @Server_Name + @DB_name + '.dbo.sp_executesql @command
    END TRY
    BEGIN CATCH
        PRINT EXCEPTION
    END CATCH

I don't think I can use RAISEERROR because I'm not running my own stored procedure, I'm only running a simple select statement. I've tried using @@ERROR but that doesn't work across a linked server either. Because the error I get is less than 20, I run into this problem:

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

http://msdn.microsoft.com/en-us/library/ms191515.aspx

I found this question: How to capture error message returned from linked server? which has not been answered either.

Answer 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