Why TransactionScope uses a distributed transaction when I use only LinqToSql and Ado.Net

Question

We are having problems on the same machine with the error message:

"MSDTC on server XXX is not available."

The code uses TransactionScope to transfer some LingToSql database code; There is also some raw Ado.net inside the transaction.

Once access to a single SQL database (2005), why is a distributed transaction used at all?

(I do not want to know how to enable MSDTC, since the code should work on the server with their current setting)

Answer 1

This almost always happens when your transaction uses multiple database connections. So let's say you update two tables. You can update the first table using one join, but updating the second table using another second join. This will cause the transaction to transition to MSDTC, even using the TransactionScope object.

As we did, when performing transactions, we use one database context object for all of our records. This eliminated the escalation. After that, we never had an MSDTC message.

Randy