Return if remote stored procedure fails

Posted by njk on Stack Overflow See other posts from Stack Overflow or by njk
Published on 2012-11-27T21:40:43Z Indexed on 2012/11/30 11:04 UTC
Read the original article Hit count: 297

I am in the process of creating a stored procedure. This stored procedure runs local as well as external stored procedures. For simplicity, I'll call the local server [LOCAL] and the remote server [REMOTE].

USE [LOCAL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[monthlyRollUp] 
AS
SET NOCOUNT, XACT_ABORT ON
BEGIN TRY
    EXEC [REOMTE].[DB].[table].[sp]

    --This transaction should only begin if the remote procedure does not fail
    BEGIN TRAN
        EXEC [LOCAL].[DB].[table].[sp1]
    COMMIT

    BEGIN TRAN
        EXEC [LOCAL].[DB].[table].[sp2]
    COMMIT

    BEGIN TRAN
        EXEC [LOCAL].[DB].[table].[sp3]
    COMMIT

    BEGIN TRAN
        EXEC [LOCAL].[DB].[table].[sp4]
    COMMIT
END TRY
BEGIN CATCH
    -- Insert error into log table
    INSERT INTO [dbo].[log_table] (stamp, errorNumber, 
        errorSeverity, errorState, errorProcedure, errorLine, errorMessage)
    SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(),
        ERROR_LINE(), ERROR_MESSAGE()
END CATCH
GO

When using a transaction on the remote procedure, it throws this error:

OLE DB provider ... returned message "The partner transaction manager has disabled its support for remote/network transactions.".

I get that I'm unable to run a transaction locally for a remote procedure.

How can I ensure that the this procedure will exit and rollback if any part of the procedure fails?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008