How to stop TransactionScope from starting distributed transaction

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I use TransactionScope in my code, I got error message saying "cannot
use SAVE TRANSACTION in distributed transaction". This is because I have SAVE
TRANSACTION in one of my stored procedures.

It looks like TransactionScope automatically starts distributed transaction
even I only access one SQL server database. Does anybody know how to stop
TransactionScope from starting distributed transactions? In my application,
it should be only local transactions.

Thanks a lot.
 
If using SQL2k5, your transaction will not promote if you are using only one
instance of SqlConnection, or physical SQL Server Connection underneath. In
other words, don't involve more than one database connection in one
transaction scope and your tx won't promote to MSDTC.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________
 
I just want to make sure I understand this correctly.

In my case, I only link to one SQL Server. But I open and close
SqlConnection object while I update different tables. So inside my
TransactionSope, I will open and close SqlConnection many times. Will this
cause TransactionScope promote MSDTC?

Thanks a lot.
 
I mean, if I only link to one SQL 2005 Server. But I open and close
SqlConnection object while I update different tables in 2 SQL 2005 databases.
So inside my TransactionSope, I will open and close SqlConnection many times.
Will this cause TransactionScope promote MSDTC?

Thanks a lot.
 
YES it will :)

Even if it is the same database, opening and closing multiple times will
actually create more than one opened connection. The connection will have to
remain open because it needs to perform a rollback, in event of a booboo.
And multiple physical connections = Tx Promotion.

So ... the answer "Yes"

SM
 
Back
Top