How can i handle connections to different databases inside a single transaction scope

Question

I'm trying to access data from two different databases inside a transactionscope. But while executing any query on the second database , i get "The operation is not valid for the state of the transaction." error

using(TransactionScope scope=new TransactionScope())
{
     Insert();//Inserts to database A
     Select();//Selects from database B - error while executing this statement
}

Both the databases exist in the same server.

Answer 1

On SQL Server 2005, I think that will require a DTC (Distributed Transaction Coordinator) to be active on all machines. The 2 different connections will cause the Transaction to be Promoted from Local to Distributed.

Apparently, that is fixed on SQL Server 2008: TransactionScope automatically escalating to MSDTC on some machines? Which version of SQL Server are you using?

see A ConnectionScope class

Common Gotchas when using TransactionScope and MS DTC