How to stop TransactionScope from starting distributed transaction

[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.

 

[Sahil Malik [MVP C#]]

Are you using SQL Server 2000? If so, your transaction will be distributed
and you can't help that.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Guest]

If I use SQL server 2005, can I use local transaction with TransactionScope?

Thanks.

 

[Sahil Malik [MVP C#]]

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
__________________________________________________________

 

[Guest]

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.

 

[Sahil Malik [MVP C#]]

yes it will

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Guest]

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.

 

[Sahil Malik [MVP C#]]

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