none
Transaction aborted with ADO.NET since installing SQL Server 2008 RRS feed

  • Question

  • Hi all,

    We are getting strange behaviour since upgrading to SQL Server 2008 (from 2005) for our application. We use ADO.NET to connect to the database and use COM+ for transactions (.NET objects within COM+).

    Since upgrading to SQL Server 2008 the same code that worked before is now reporting the following exception:

    "-  [System.Transactions.TransactionAbortedException] {"The transaction has aborted."} System.Transactions.TransactionAbortedException"

    Inner exceptions:

    +  [System.Transactions.TransactionPromotionException] {"Failure while attempting to promote transaction."} System.Transactions.TransactionPromotionException

    +  [System.Data.SqlClient.SqlException] {"There is already an open DataReader associated with this Command which must be closed first."} System.Data.SqlClient.SqlException

    The code that is failing it trying to open a new SQLConnection. This only occurs when we do this and there is an existing open SQLDataReader on another SQLConnection object and we are running inside a .NET object that is "Requires Transactions" in COM+.

    Note: The other SQLConnection object is using the same ConnectionString but I guess as we are using connection pooling it may actually be the same SQLConnection at runtime?

    Also, the code runs fine when the .NET object is set to not require COM+ transactions. We are not using any SQLCommands we only use SQLConnections and SQLDataReaders.

    Is anyone aware why this would start happening since upgrading to SQL Server 2008? I am not convinced it is only to do with SQL Server 2008 as I also have SQL Server 2005 installed and when I now point to that database it also happens. But this did not happen before SQL Server 2008 was installed so would it be because of new .NET providers being installed and having different behaviour?

    In addition, if we set MultipleActiveResultSets=True in the connection string the issue does not occur. So is it true if you use different SQLConnection objects in code then at runtime it could be using the same SQLConnection because of connection pooling? But why would this only happen if we use COM+ transactions?

    Many thanks for any help provided.

    Charles Gamble.

     

     

    Thursday, April 15, 2010 10:13 AM

All replies

  • Hi all,

    I guess no-one has any ideas on this one?

    Charles.

     

    Friday, April 23, 2010 8:02 AM
  • Hi,

    Did anyone see this issue before?

    Or can someone provide some insight as to why this might be happening?

    Many thanks,

         CharlesG

     

    Monday, June 7, 2010 3:49 PM
  • Hi all,

     

    This thread seems to be similar issue to what we have:

     

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/5a677941-cbd6-4566-a763-bf797226bf15

     

    It seems to be in SQL Server 2000 when it you created a DB connection in a TX it was always a DTC transaction, then in SQL Server 2005 it was changed to only create a local transaction first and then if you connected again in the same transaction it promoted it to a DTC transaction. Which I believe is what is happening for us, and it can’t promote it because of the open DataReader.

     

    How is it possible to specify when you open a SQLConnection that it will always use a DTC transaction rather than a local transaction? Note: We are using COM+ for transactions.

    Thanks,

        CharlesG.

    Monday, June 7, 2010 4:20 PM
  • Hi CharlesG,

    Here is one way you can force SqlClient to use a DTC transaction:

        static void DoDistributedTran_GetTransmitterPropagationToken(ThreadInfo wi)
        {
          // Do a distributed transaction.
          using (TransactionScope ts1 = new TransactionScope())
          {
            // This call promotes ts1 to dtc.
            System.Transactions.TransactionInterop.GetTransmitterPropagationToken(System.Transactions.Transaction.Current);
            using (SqlConnection c1 = MakeDbConnection())
            {
              c1.Open();
              using (SqlCommand cmd = GetCommand(c1, wi))
              {
                cmd.ExecuteNonQuery();
              }
            }
            ts1.Complete();
          }
        }

    Cheers,

    Jared


    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, June 10, 2010 8:10 PM
  • Hi,

     

    Did you find a solution for this?

     

    Thanks

    Friday, July 23, 2010 9:18 PM
  • We had to change our code to not have an existing open DataReader even though it was using a different SqlConnection object. This seems like a really big gotcha!

    Regards,

         Charles.

     

    Saturday, July 24, 2010 8:19 AM
  • I had the exactly the same problem and after surfing the net for 2 days managed to find the solution . The issue was in the connection string. Please see this post which I have posted

     

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/d11e9cca-08bb-4416-a59f-cdad7e4ba1ba

     

    Hope this can solve your issue.

     

    Malini

     

    Friday, August 26, 2011 6:56 AM