|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to stop TransactionScope from starting distributed transactionWhen 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. 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 ---------------------------------------------------------------------------- Show quote "BF" <B*@discussions.microsoft.com> wrote in message news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... > 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 I use SQL server 2005, can I use local transaction with TransactionScope?
Thanks. Show quote "Sahil Malik [MVP C#]" wrote: > 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 > ---------------------------------------------------------------------------- > > > > > "BF" <B*@discussions.microsoft.com> wrote in message > news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... > > 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 __________________________________________________________ Show quote "BF" <B*@discussions.microsoft.com> wrote in message news:CFDBDFD9-E53F-40D9-B4A2-E3AFB2CE7708@microsoft.com... > If I use SQL server 2005, can I use local transaction with > TransactionScope? > > Thanks. > > "Sahil Malik [MVP C#]" wrote: > >> 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 >> ---------------------------------------------------------------------------- >> >> >> >> >> "BF" <B*@discussions.microsoft.com> wrote in message >> news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... >> > 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. >> > >> >> >> 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. Show quote "Sahil Malik [MVP C#]" wrote: > 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 > __________________________________________________________ > > > "BF" <B*@discussions.microsoft.com> wrote in message > news:CFDBDFD9-E53F-40D9-B4A2-E3AFB2CE7708@microsoft.com... > > If I use SQL server 2005, can I use local transaction with > > TransactionScope? > > > > Thanks. > > > > "Sahil Malik [MVP C#]" wrote: > > > >> 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 > >> ---------------------------------------------------------------------------- > >> > >> > >> > >> > >> "BF" <B*@discussions.microsoft.com> wrote in message > >> news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... > >> > 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. > >> > > >> > >> > >> > > > yes it will
-- Show quote- Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx __________________________________________________________ "BF" <B*@discussions.microsoft.com> wrote in message news:2BD58427-A14D-4B2B-9ECB-02B0ABCB1EF9@microsoft.com... >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#]" wrote: > >> 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 >> __________________________________________________________ >> >> >> "BF" <B*@discussions.microsoft.com> wrote in message >> news:CFDBDFD9-E53F-40D9-B4A2-E3AFB2CE7708@microsoft.com... >> > If I use SQL server 2005, can I use local transaction with >> > TransactionScope? >> > >> > Thanks. >> > >> > "Sahil Malik [MVP C#]" wrote: >> > >> >> 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 >> >> ---------------------------------------------------------------------------- >> >> >> >> >> >> >> >> >> >> "BF" <B*@discussions.microsoft.com> wrote in message >> >> news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... >> >> > 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. >> >> > >> >> >> >> >> >> >> >> >> 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. Show quote "Sahil Malik [MVP C#]" wrote: > yes it will > > -- > > - Sahil Malik [MVP] > ADO.NET 2.0 book - > http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx > __________________________________________________________ > "BF" <B*@discussions.microsoft.com> wrote in message > news:2BD58427-A14D-4B2B-9ECB-02B0ABCB1EF9@microsoft.com... > >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#]" wrote: > > > >> 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 > >> __________________________________________________________ > >> > >> > >> "BF" <B*@discussions.microsoft.com> wrote in message > >> news:CFDBDFD9-E53F-40D9-B4A2-E3AFB2CE7708@microsoft.com... > >> > If I use SQL server 2005, can I use local transaction with > >> > TransactionScope? > >> > > >> > Thanks. > >> > > >> > "Sahil Malik [MVP C#]" wrote: > >> > > >> >> 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 > >> >> ---------------------------------------------------------------------------- > >> >> > >> >> > >> >> > >> >> > >> >> "BF" <B*@discussions.microsoft.com> wrote in message > >> >> news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... > >> >> > 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. > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > > 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 Show quote "BF" <B*@discussions.microsoft.com> wrote in message news:17202F17-D19F-4646-8054-A643157E622D@microsoft.com... >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#]" wrote: > >> yes it will >> >> -- >> >> - Sahil Malik [MVP] >> ADO.NET 2.0 book - >> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx >> __________________________________________________________ >> "BF" <B*@discussions.microsoft.com> wrote in message >> news:2BD58427-A14D-4B2B-9ECB-02B0ABCB1EF9@microsoft.com... >> >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#]" wrote: >> > >> >> 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 >> >> __________________________________________________________ >> >> >> >> >> >> "BF" <B*@discussions.microsoft.com> wrote in message >> >> news:CFDBDFD9-E53F-40D9-B4A2-E3AFB2CE7708@microsoft.com... >> >> > If I use SQL server 2005, can I use local transaction with >> >> > TransactionScope? >> >> > >> >> > Thanks. >> >> > >> >> > "Sahil Malik [MVP C#]" wrote: >> >> > >> >> >> 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 >> >> >> ---------------------------------------------------------------------------- >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "BF" <B*@discussions.microsoft.com> wrote in message >> >> >> news:9A5DB05A-BA0F-480D-8E4E-55F6EDC0362F@microsoft.com... >> >> >> > 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. >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics