The most common usage of regular / external database connections in SQLCLR objects, it would seem, is to do
SELECT-only queries (i.e. no changes: DML, DDL, DCL, D*%$#!L, etc). It is also fairly common to add the Connection String option
Enlist=false given various restrictions imposed when
Enlist=true (the default when the
Enlist keyword is not specified;
Enlist means whether or not to join an existing transaction).
If there is a reason to make changes via that regular / external connection, then having
Enlist=false causes those changes to be independent and not part of an active transaction started in the context of the calling query. Meaning, using
Enlist=false has the following behavior:
BEGIN TRAN; -- start with no rows in [LocalTable] INSERT INTO SchemaName.LocalTable (Column) VALUES (N'ValueA'); EXEC SchemaName.SqlClrInsertIntoLocalTable @TextToInsert=N'ValueB'; ROLLBACK; SELECT * FROM SchemaName.LocalTable; -- returns 1 row containing 'ValueB'
Ok. So, if we did want the changes made in
SqlClrInsertIntoLocalTable to be bound by the explicit transaction started in the calling context (i.e. the
BEGIN TRAN; statement), then we would either specify
Enlist=true or not specify
Enlist in the first place as
true is the default value.
The first attempt might get the following error:
MSDTC on server 'ServerName\InstanceName' is unavailable.
Ok, so go into Control Panel \ System and Security \ Administrative Tools \ Services and click start on "Distributed Transaction Coordinator".
Try the SQLCLR Stored Procedure again and this time get the following error:
The transaction has aborted.
So, is it possible to bind an external connection to an existing transaction? Do distributed transactions even work with
SqlConnection? Creating a simple Console App using TransactionScope to wrap two separate connections shows that it does indeed work. Going back to the SQLCLR object, adding
TransactionScope does not work.
Of course, there are three possible values for TransactionScopeOption. Using:
Suppress: works just like
Enlist=false, specifically preventing any binding of the separate connection.
RequiresNew: no apparent effect; works just like
Required: gets the lovely "The transaction has aborted" error.
(I will post my test code to PasteBin.com later, once I clean it up)
So, am I missing something / doing something wrong? I had always just assumed that doing distributed transactions in SQLCLR objects was possible, provided MSDTC was running and
Enlist=true and using
TransactionScope. But I cannot get it to work. Is this a limitation of working with SQLCLR objects? It certainly seems like it, but I could be missing a required step.
The Assembly is marked as
WITH PERMISSION_SET = UNSAFE, but I have also tried with
EXTERNAL_ACCESS. I am using
UNSAFE at the moment because some transactional stuff appears to require Full Trust, though nothing that I am currently using. I had originally just set the DB to
TRUSTWORTHY ON and not even signed the Assembly. However, I have now signed the Assembly, created the Asymmetric Key and Key-based Login, and granted that Login the
UNSAFE ASSEMBLY permission. I then re-ran the tests just to make sure that the issue wasn't due to a lack of signing (which does help in at least one case I am aware of). I still get the "The transaction has aborted" error.
I have tried with both
BEGIN DISTRIBUTED TRAN and
I found one resource, Troubleshooting MSDTC Permission Issues When a Distributed Transaction Starts, that indicates some problems with MSDTC are related to permissions. While that post is mainly focused on scenarios involving clustering, there are still some parts that seem like they could be relevant. Item #5 is specific to MSDTC. I ran
sc sdshow msdtc and it showed that all relevant groups have the correct
CC permission (among others).
As it relates to the permission, I am running the tests on SQL Server Express 2014 LocalDB, hence the security context of both the local and remote requests is that of my Windows Login. And because LocalDB runs as a background process instead of as a Service, there should be none of the "odd" behavior that sometimes results from Impersonation. And, the "external" connection is still the local machine, so I am only dealing with a single Transaction Manager/DTC.
The stack traces for the errors are as follows:
TransactionScopeis not being used:
Msg 6522, Level 16, State 1, Procedure TestProc, Line 165 A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc": System.Transactions.TransactionAbortedException: The transaction has aborted. System.Transactions.TransactionAbortedException: at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx) at System.Transactions.Transaction.Promote() at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte whereabouts) at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte whereAbouts) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(Task...
TransactionScopeis being used (ScopeOption =
Msg 6522, Level 16, State 1, Procedure TestProc, Line 194 A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc": System.Transactions.TransactionAbortedException: The transaction has aborted. System.Transactions.TransactionAbortedException: at System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx) at System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking) at System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption) at System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent) at System.Transactions.TransactionScope.PushScope() at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TimeSpan scopeTimeout, TransactionScopeAsyncFlowOption asyncFlowOption) at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TimeSpan scopeTimeout) at ContextConnectionTests.TestProc(SqlString Scope, SqlString ConnectionString)
MSDTC is definitely registering the attempts at distributed transactions and that they are "aborted". I can see by opening Control Panel\System and Security\Administrative Tools\Component Services and then going to Component Services \ Computers \ My Computer \ Distribute Transaction Coordinator\Local DTC\Transcation Statics.
I tried viewing the MSDTC log file using the directions found here: Viewing Trace Data, but was not able to get it working. I have the required programs, but then I just get error messages about how it can't open any of the files for wrinting.
I did finally get this working, but in a very limited context. I have a VM running SQL Server 2008 on XP and it was able to run the same SQLCLR Assembly to do a Distributed Transaction to connect to the host OS (Windows 8) running SQL Server 2012.
I also had to temporarily disable Windows Firewall on both since I couldn't find the specific program name and/or ports to allow. I wouldn't recommend this for anything but testing and will eventually have to figure those details out.
I was not, however, able to get it working if the Transaction originated on the SQL Server 2012 side. I will try to find time soon to update my test code to post (originally it only had a single ConnectionString parameter as I was testing on the same instance, but then found a note in some MSDN documentation saying that DTC wouldn't work on the same instance, hence I need to clean up my very hurried modifications ;-).