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)
{
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)
{
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 ( ).
?