CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresremote-server
Kermit
Kermit

Reputation: 34063

Return if remote stored procedure fails

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].

Here's a simple topology:

The procedure

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?

Notes

Upvotes: 2

Views: 4143

Answers (4)

StrayCatDBA
StrayCatDBA

Reputation: 2880

If you can't or don't want to use DTC, and don't want to use CLR, then then you need to call the remote sp last, as you won't be able to rollback the remote sp call.

SET NOCOUNT, XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF;
BEGIN TRY
    DECLARE @ret INT
    BEGIN TRAN
        --Perform these in a transaction, so they all rollback together
        EXEC [LOCAL].[DB].[table].[sp1]
        EXEC [LOCAL].[DB].[table].[sp2]
        EXEC [LOCAL].[DB].[table].[sp3]
        EXEC [LOCAL].[DB].[table].[sp4]
    --We call remote sp last so that if it fails we rollback the above transactions
    --We'll have to assume that remote sp takes care of itself on error.
    EXEC [REMOTE].[DB].[table].[sp] 

    COMMIT
END TRY
BEGIN CATCH
    --We rollback
    ROLLBACK       
    -- 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

If the local sp's depend on results from the remote stored procedure, then you can use a CLR sp (will need EXTERNAL_ACCESS permissions) and manage the transactions explicitly (basically, a roll your own DTC, but no two-phase commit. You're effectively delaying the remote commit.)

//C# fragment to roll your own "DTC"  This is not true two-phase commit, but 
//may be sufficient to meet your needs.  The edge case is that if you get an error
//while trying to commit the remote transaction, you cannot roll back the local tran.
using(SqlConnection cnRemote = new SqlConnection("<cnstring to remote>")) 
{
  try {

    cnRemote.Open();
    //Start remote transaction and call remote stored proc
    SqlTransaction trnRemote = cnRemote.BeginTransaction("RemoteTran");
    SqlCommand cmdRemote = cnRemote.CreateCommand();
    cmdRemote.Connection = cnRemote;
    cmdRemote.Transaction = trnRemote;
    cmdRemote.CommandType = CommandType.StoredProcedure;
    cmdRemote.CommandText = '[dbo].[sp1]';
    cmdRemote.ExecuteNonQuery();

        using(SqlConnection cnLocal = new SqlConnection("context connection=true")) 
        {
            cnLocal.Open();
            SqlTransaction trnLocal = cnLocal.BeginTransaction("LocalTran");
            SqlCommand cmdLocal = cnLocal.CreateCommand();
            cmdLocal.Connection = cnLocal;
            cmdLocal.Transaction = trnLocal;

            cmdLocal.CommandType = CommandType.StoredProcedure;
            cmdLocal.CommandText = '[dbo].[sp1]';
            cmdLocal.ExecuteNonQuery();
            cmdLocal.CommandText = '[dbo].[sp2]';
            cmdLocal.ExecuteNonQuery();
            cmdLocal.CommandText = '[dbo].[sp3]';
            cmdLocal.ExecuteNonQuery();
            cmdLocal.CommandText = '[dbo].[sp4]';
            cmdLocal.ExecuteNonQuery();

            //Commit local transaction
            trnLocal.Commit();

        }
        //Commit remote transction
        trnRemote.Commit();
    } // try
    catch (Exception ex)
    {
        //Cleanup stuff goes here.  rollback remote tran if needed, log error, etc.
    }
}

Upvotes: 0

b0rg
b0rg

Reputation: 1897

IMO easiest way is to

  • Add Return value to remote proc.
  • Wrap remote proc into transaction and try catch (inside remote proc). If error happened return false.
  • On local stored proc if false, simply do not continue.

I also fail to understand the reason behind multiple BEGIN TRANS / COMMIT in the local proc. I mean if this is month end rollup, shuldn't this be one big transaction rather than a bunch of small? Otherwise your trans 1 and 2 may commit successfully, but 3 will fail and that's that.

Names are made up ofc:

 CREATE PROC [remote].db.REMOTE_PROC ( 
      @return_value int output
 ) 
 AS 
 BEGIN 
      SET XACT_ABORT ON; 
      BEGIN TRY      
           BEGIN TRANS 
           ... do stuff ... 

                set @return_value = 1;
           COMMIT; 
      END TRY 
      BEGIN CATCH 
           set @return_value = 0; 
      END CATCH
 END

and the local proc

 CREATE PROC [local].db.[monthlyRollUp] AS
 BEGIN 
      SET XACT_ABORT ON; 

      declare @ret int; 

      EXECUTE [remote].dbo.REMOTE_PROC @return_value = @ret OUTPUT; 

      IF @ret = 0 
           PRINT 'ERROR :(' 
           RETURN
      END IF 

      BEGIN TRANS 
           -- one big transaction here 
           EXEC [LOCAL].[DB].[table].[sp1]; 

           EXEC [LOCAL].[DB].[table].[sp2]; 

           EXEC [LOCAL].[DB].[table].[sp3]; 

           EXEC [LOCAL].[DB].[table].[sp4]; 

      COMMIT; 

 END;

afair [remote].dbo.REMOTE_PROC runs its own transaction space, and returns 1 if successful. Local proc, checks the return value and decides whether to proceed or not.

sp1 sp2 sp3 and sp4 are all running in one single transactions, as having multiple transactions for each of them does not really make much sense to me.

Upvotes: 4

AaronLS
AaronLS

Reputation: 38364

I might be a little unclear on what you want. If you need the entire monthlyRollUp SP to rollback on a failure of either the remote or local procedures, then you will need a distributed transaction coordinator. This will allow the servers to communicate the information about the transaction and coordinate the commits. I.e., both servers have to indicate that all necessary locks were gained and then coordinate commits on both servers so that the operation is automic. Here is one example of setting up a DTC: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/

If you don't want the remote procedures to participate/affect the transaction, you can try setting:

SET REMOTE_PROC_TRANSACTIONS OFF;

http://msdn.microsoft.com/en-us/library/ms178549%28SQL.90%29.aspx

I haven't used that setting before though so I'm not sure if it will accomplish what you need.

Upvotes: 0

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

You can try to execute both stored procedure into seperate TRY CATCH block and check for corresponding ERROR_NUMBER in CATCH block. If ERROR_NUMBER is same as error you are getting you can simply return or raiseerror as per your requirement.

Is it causing a fatal error. Please check what error severity is in the exception.

Upvotes: 0

Related Questions