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:

1. 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();
               }
           }
       }
   }
   

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

3. 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 ?

Answer 1

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.