Is it possible to use .NET's TransactionScope with Sql Server 2005 without allowing promotion to DTC?

Question

I'm dealing with some really pain in the ass servers, and I'd like them to just use transactions without DTC (for now so I can concentrate elsewhere). I use multiple databases within the scope, so the typical behavior is to promote, but I want to avoid it. What would the behavior be of an in-doubt transaction under this model, if it's possible? I assume tx.Complete() will throw?

The reason for this is because I get these random errors (feel free to chime in on why for this too if you can maybe help - these are SPORADIC, not constant):

System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. ---> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component.

   at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)

   at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)

   --- End of inner exception stack trace ---

   at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)

   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)

   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)

   at System.Transactions.EnlistableStates.Promote(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.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)

   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)

   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

Answer 1

Unfortunately, I don't think it's possible to avoid having the transaction promoted to a DTC transaction when multiple databases are concerned, or even a single database using multiple connection objects.

Answer 2

Two separate connections under the same TransactionScope must enroll into DTC. You can either:

• make sure you never open more than 1 connection under each transaction scope
• or override the current transaction scope with a new one when opening a new connection (thus breaking transactional consistency in the process...)

You can't 'ignore' this aspect and leave it for later, transactional consistency is the fundation of all database operations and not some night-before-release afterthought. You must get this right before you write the very first line of code.

Answer 3

I feel your pain, but in over 4 years using that exact same tech stack I have yet to find a way to avoid DTC.

While your application probably will not actually promote your queries to use MSDTC (assuming you use a single database and a single connection string) the Transactions namespace still spins up and checks to make sure MSDTC is there and available.

As far as I've been able to determine (and I've put a TON of time into it)... If it cannot talk to MSDTC (even it will not use it in the end), then it will throw an exception. Every time. Period.

[EDIT:] If you ARE using multiple databases, then you are in an even worse place as the Transactions system delegates coordination to MSDTC when multiple resources are needed.

Answer 4

The only way you can avoid DTC when doing transactions (and using transactionscope) is if you use only one instance of SQL Server 2008 and .Net 3.0.

Answer 5

Check out MultipleActiveResultSets connection string parameter