How to use TransactionScope in SQLCLR without escalation to MSDTC

Question

A lot of our DAL code uses TransactionScope for transactions. That works great but there is a problem when i use this DAL code from inside an SQLCLR procedure. The Transaction is escalated to MSDTC what i don't want.

The Problem can be reproduced easily:

CLR Implementation

[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

the 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 the "BEGIN TRANSACTION" statement, the dbo.ClrWithScope call works OK. I suppose that the transaction started by SQLServer is not considered while enlisting in the .Net Framework.

Is there a solution to get arround this. An idea is to manually create an SqlTransaction and make the TransactionScope to use this transaction, but i don't know how to do this. Another solution would be to make a special case in all of the DAL code (not really funny to implement).

Any ideas ?

@Magnus But that doesn't (automatically) enlist the Connection into the ambient transaction, which is the whole purpose of the TransactionScope, isn't it? — Christian.K, Aug 27 '12 at 08:18
Christian.K is right. The DAL code is using TransactionScope and SqlConnection a lot. Of course it is possible to rewrite the whole codebase in DAL but it is not nice. — Stefan Schönbächler, Aug 27 '12 at 09:04
Maybe I'm missing something, but why is it escalating to DTC at all? That should only happen if you are connecting/executing on a second Server/Instance, but there's no indication of that anywhere in here? — RBarryYoung, Aug 27 '12 at 10:37
@RBarryYoung. Under some circunsances (nesting, SqlServer versions) the Transaction is escalation although only one connection string is used. See [link] (http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines?rq=1) for more information. Here, the transaction is only escalated when BEGIN TRANSACTION in SQL Script is used. — Stefan Schönbächler, Aug 27 '12 at 11:13

score 1 · Accepted Answer · answered May 07 '14 at 13:38

Using TransactionScope within SQL CLR will always promote/escalate to a MSDTC transaction. There does not appear to be any way around this, even in SQL 2012.

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

TransactionScope should be used only when local and remote data sources or external resource managers are being accessed. This is because TransactionScope always causes transactions to promote, even if it is being used only within a context connection.

How to use TransactionScope in SQLCLR without escalation to MSDTC

1 Answers1