Handling Errors in Server-to-Server Remote Stored Procedures
When you run remote stored procedures and batches from a local instance of SQL Server to a client, you can experience statement and batch abort errors:
When a statement abort error occurs, the statement that caused the error is terminated, but the execution of the remote stored procedure or batch continues.
When a batch abort error occurs, the execution of the complete remote stored procedure or batch terminates.
When remote stored procedures and batches are executed within the scope of a TRY block, batch abort errors can be handled by the TRY…CATCH construct.
The behavior of remote stored procedures and batches resulting from statement and batch abort errors depends on the SET XACT_ABORT setting of the local server.
SET XACT_ABORT Is OFF
If SET XACT_ABORT is OFF in the local server, any statement abort errors in the remote stored procedure are propagated as statement abort errors by the local server to the client. Only the statement that caused the error is terminated. The client receives error messages that correspond to the statement abort errors. Also, if the remote stored procedure runs until it is completed, @@ERROR returns 0. If the error occurs within the scope of a TRY block, execution continues and the CATCH block is not invoked.
Any batch abort errors in the remote stored procedure are propagated by the local server to the client. The EXECUTE statement that called the remote stored procedure terminates, but the batch or stored procedure that contained the EXECUTE statement continues running. Therefore, @@ERROR returns the error code that corresponds to the error that terminated the remote stored procedure, and the return value of the stored procedure is NULL. When the remote stored procedure that generates an error is executed within the scope of a TRY block on the local server, the error causes control to pass to the CATCH block with information about the last error on the remote server.
When you troubleshoot errors, execute a remote stored procedure from within the TRY block of a TRY…CATCH construct. If the remote stored procedure is not completed successfully, execution jumps to the associated CATCH block on the local server with information about the last error on the remote server. If the remote stored procedure is completed successfully, execution continues within the TRY block on the local server and the return value of the remote stored procedure can be used.
Alternatively, when executing a remote stored procedure outside the scope of a TRY block, examine @@ERROR at the end of the remote stored procedure to determine whether the remote procedure is completed. If @@ERROR is 0, the remote stored procedure executed successfully and the return value of the stored procedure can be used. If @@ERROR is not zero, the remote stored procedure was not completed successfully and the return value of the stored procedure cannot be used.
SET XACT_ABORT Is ON
If SET XACT_ABORT is ON in the local server, the setting is propagated to the linked server. All statement and batch abort errors in the remote stored procedure are converted to batch abort errors in the local server. Therefore, execution of the batch or stored procedure that called the remote stored procedure is terminated together with the remote stored procedure. When the remote stored procedure that generates an error is executed within the scope of a TRY block on the local server, the error causes control to pass to the CATCH block with information about the last error on the remote server.
When a remote stored procedure is executed outside the scope of a TRY block, you cannot examine the value of @@ERROR to determine that a batch error occurred because the statement after the EXECUTE statement does not execute. Therefore, you should execute a remote stored procedure from within the TRY block of a TRY…CATCH construct. If the remote stored procedure does not complete successfully, execution jumps to the associated CATCH block on the local server with information about the last error on the remote server. If the remote stored procedure completes successfully, execution continues within the TRY block on the local server and the return value of the remote stored procedure can be used.
RAISERROR and TRY…CATCH
Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. 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. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.