如果远程存储过程失败，返回

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

我正在创建一个存储过程。这个存储过程运行本地和外部存储过程。为了简单起见，我将调用本地服务器[local]和远程服务器[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.".

OLE DB提供者……返回的消息“合作伙伴事务管理器已禁用它对远程/网络事务的支持。”

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

笔记

• With regards to combining the simple procedures, some of them are used individually.
• 关于结合简单的程序，有些是单独使用的。

4 个解决方案

#1

IMO easiest way is to

在我看来最简单的方法就是

• Add Return value to remote proc.
• 向远程proc添加返回值。
• Wrap remote proc into transaction and try catch (inside remote proc). If error happened return false.
• 将远程proc封装到事务中并尝试捕获(在远程proc中)。如果错误发生返回false。
• On local stored proc if false, simply do not continue.
• 在本地存储的proc上，如果为false，请不要继续。

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.

我也不明白本地proc中多个BEGIN TRANS / COMMIT背后的原因。我的意思是如果这是一个月的end rollup，难道这不是一个大交易而不是一堆小交易吗?否则你的反式1和2可能会成功提交，但3会失败，仅此而已。

Names are made up ofc:

名称由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

和当地的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.

一家集(远程).dbo。REMOTE_PROC运行自己的事务空间，如果成功，返回1。本地proc检查返回值并决定是否继续。

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.

sp1 sp2 sp3和sp4都在一个事务中运行，因为对于每个事务都有多个事务对我来说并没有多大意义。

#2

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.

您可以尝试在分离的try CATCH块中执行两个存储过程，并在CATCH块中检查相应的ERROR_NUMBER。如果ERROR_NUMBER与您得到的错误相同，您可以根据需要简单地返回或查询错误。

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

它会导致致命的错误吗?请检查异常中的错误严重性。

#3

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/

我可能不太清楚你想要什么。如果您需要整个月卷SP来回滚远程或本地过程的失败，那么您将需要一个分布式事务协调器。这将允许服务器通信有关事务的信息并协调提交。即。，两个服务器都必须表明获得了所有必需的锁，然后在两个服务器上协调提交，以便操作是自动的。这里有一个建立DTC的例子:http://social.msdn.microsoft.com/forums/en- us/adodotnetdataproviders/thread/71722f -acbe- 442 -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

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.

我以前没有使用过这个设置，所以我不确定它是否能满足你的需要。

#4

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.

如果您不能或不想使用DTC，并且不想使用CLR，那么您需要最后调用远程sp，因为您无法回滚远程sp调用。

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

如果本地sp依赖于远程存储过程的结果，那么您可以使用CLR sp(将需要EXTERNAL_ACCESS权限)并显式地管理事务(基本上，卷自己的DTC，但不进行两阶段提交)。有效地延迟了远程提交。

//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.
    }
}