none
Problem with Transaction Scope and MSDTC RRS feed

  • Question

  • Hi

    I have a problem with this simple code snipped:

     
    Code Snippet

    class Program
        {
            static void Main(string[] args)
            {
                using (TransactionScope loScope = new TransactionScope())
                {
                    try
                    {
                        using (SqlConnection loConn1 = new SqlConnection("Data Source=DBServer;Initial Catalog=DB;User ID=XXX;Password=ZZZ;Connect Timeout=3600;Application Name=App"))
                        {
                            loConn1.Open();

                            loConn1.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                    loScope.Complete();
                }
            }
        }


    When I want to open the connection the transaction is promoted to msdtc. As I understand, this happens only when you open a second connection inside the transaction scope.

    I am working on Windows XP Professional with VS 2005 and a remote SQL2000 Server.

    Has somebody a idea why this happens?
    Monday, September 10, 2007 11:43 AM

Answers

  • Try the blog post below Microsoft employee is trying to explain you need MSDTC if you are using SQL Server 2000 and not with 2005. The reason ANSI SQL transaction is a unit of work that must be atomic but System.Transaction TransactionScope class is not atomic because you can add many units of work in one Scope, 2005 can handle it but 2000 and below and Oracle cannot handle it out of the box.  Hope this helps.

     

    http://blogs.msdn.com/angelsb/archive/2004/07/06/174215.aspx

     

    Monday, September 10, 2007 1:52 PM
  • The person who posted that explanation did it as a member of the Asp.net team before moving to the ADO.NET team, I think the SQL Server team thinks the transaction programming team should document it but they did not do it because technically System.Transaction is application layer code Sqlconnection related problems are other departments business not their problem.  I try to explain SQL Server not equiped to handle it will reject it because it is not atomic to users as needed even before finding the blog post.  You could use ADO.NET transaction because those use Save points for nesting making all transactions atomic.  Microsoft usually don't document these math related ANSI SQL issues with their product.

     

    http://msdn2.microsoft.com/en-us/library/ms971557.aspx

     

    Monday, September 10, 2007 8:33 PM

All replies

  • Try the blog post below Microsoft employee is trying to explain you need MSDTC if you are using SQL Server 2000 and not with 2005. The reason ANSI SQL transaction is a unit of work that must be atomic but System.Transaction TransactionScope class is not atomic because you can add many units of work in one Scope, 2005 can handle it but 2000 and below and Oracle cannot handle it out of the box.  Hope this helps.

     

    http://blogs.msdn.com/angelsb/archive/2004/07/06/174215.aspx

     

    Monday, September 10, 2007 1:52 PM
  • Thanks for the quick answer.

    This is the explanation why it works on the pc of our ms consultant (he uses sql 2005).


    Perhaps this should be mentioned in the documentation. It had saved me time and brain cells.
    Monday, September 10, 2007 2:12 PM
  • The person who posted that explanation did it as a member of the Asp.net team before moving to the ADO.NET team, I think the SQL Server team thinks the transaction programming team should document it but they did not do it because technically System.Transaction is application layer code Sqlconnection related problems are other departments business not their problem.  I try to explain SQL Server not equiped to handle it will reject it because it is not atomic to users as needed even before finding the blog post.  You could use ADO.NET transaction because those use Save points for nesting making all transactions atomic.  Microsoft usually don't document these math related ANSI SQL issues with their product.

     

    http://msdn2.microsoft.com/en-us/library/ms971557.aspx

     

    Monday, September 10, 2007 8:33 PM
  • I revised our transaction layer and it now uses ado transactions and it works.


    Thanks for the help.
    Tuesday, September 11, 2007 7:32 AM