How to use TransactionScope in SQLCLR without escalation in MSDTC

Many of our DAL codes use TransactionScope for transactions. This works fine, but there is a problem when I use this DAL code from the SQLCLR procedure. The transaction went to MSDTC, which I do not want.

The problem can be easily reproduced:

  • CLR execution

    [SqlProcedure]
    public static void ClrWithScope(string cmdText)
    {
        /* escalates to MSDTC when a transaction is already open */
        using ( var scope = new TransactionScope())
        {
            using (var connection = new SqlConnection("context connection=true;"))
            {
                connection.Open();
                using (var cmd = new SqlCommand(cmdText, connection))
                {
                    SqlContext.Pipe.ExecuteAndSend(cmd);
                }
            }
            scope.Complete();
        }
    }
    
    [SqlProcedure]
    public static void ClrWithTrans(string cmdText)
    {
        /* works as expected (without MSDTC escalation ) */
        using (var connection = new SqlConnection("context connection=true;"))
        {
            connection.Open();
            using (var tx = connection.BeginTransaction())
            {
                using (var cmd = new SqlCommand(cmdText, connection, tx))
                {
                    SqlContext.Pipe.ExecuteAndSend(cmd);
                    tx.Commit();
                }
            }
        }
    }
    

  • SQL script used to execute the CLR procedure

    BEGIN TRANSACTION
    
    exec dbo.ClrWithTrans "select * from sys.tables";
    exec dbo.ClrWithScope "select * from sys.tables"; /* <- DOES NOT WORK! */
    
    ROLLBACK TRANSACTION
    
  • error

    Msg 6549, Level 16, State 1, Procedure ClrWithScope, Line 0
    A .NET Framework error occurred during execution of user defined routine or aggregate 'clrClrWithScope': 
    System.Transactions.TransactionAbortedException: Die Transaktion wurde abgebrochen. ---> System.Transactions.TransactionPromotionException: MSDTC on server 'BLABLA' is unavailable. ---> System.Data.SqlClient.SqlException: MSDTC on server 'BLABLA' is unavailable.
    System.Data.SqlClient.SqlException: 
       bei System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
       bei System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
    System.Transactions.TransactionPromotionException: 
       bei System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
       bei System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
       bei System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
    System.Transactions.TransactionAbortedException: 
       bei System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx)
       bei System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)
       bei System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)
       bei System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)
       bei System.Transactions.TransactionScope.PushScope()
       bei System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption)
       bei Giag.Silo.Data.SqlClr.ClrWithScope(String cmdText)
    . User transaction, if any, will be rolled back.
    

WIHTOUT: BEGIN TRANSACTION statement, dbo.ClrWithScope call works fine. I believe that the transaction launched by SQLServer is not considered when connecting .Net Framework.

. , SqlTransaction TransactionScope , , . DAL ( ).

?

+5
1

TransactionScope SQL CLR / MSDTC. , , SQL 2012.

TechNet SQL CLR TransactionScope (http://technet.microsoft.com/en-us/library/ms131084.aspx)

TransactionScope , . TransactionScope , .

+1

All Articles