Problem with TransactionScope and SqlDataReader with SQL Server 2005?

I developed some C#.NET 2.0 code that performed perfectly with SQL Server 2000 but is now bombing out with SQL Server 2005. I've been able to duplicate this behavior in every single construct that looks like this:

public void SqlMethod1()
{
    using (TransactionScope ts = new TransactionScope())
    {
        using (SqlConnection con = new SqlConnection(DataManager.DSN))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = <any SQL string>;
                using (SqlDataReader rdr = cmd.ExecuteReader
                   (CommandBehavior.CloseConnection))
                {
                    while (rdr.Read())
                    {
                        SqlMethod2();
                    }
                    rdr.Close();
                }
            }
        }
        ts.Complete();
    }
}

private void SqlMethod2()
{
    using (SqlConnection con = new SqlConnection(DataManager.DSN))
    {
        con.Open()
        ...

This always ran with SQL Server 2000 as the database engine. With Sql Server 2005, if I comment out the transaction, the code runs perfectly. With the transaction in place, the code runs as far as con.Open(), and then I get the following exception:

[SqlException (0x80131904): There is already an open DataReader associated with this Command which must be closed first.]
  System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction) +406
  System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction) +163
  System.Data.SqlClient.SqlDelegatedTransaction.Promote() +92

[TransactionPromotionException: Failure while attempting to promote transaction.]
  System.Data.SqlClient.SqlDelegatedTransaction.Promote() +127
  System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx) +55
  System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx) +201

[TransactionAbortedException: The transaction has aborted.]
  System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx) +11
  System.Transactions.EnlistableStates.Promote(InternalTransaction tx) +22
  System.Transactions.Transaction.Promote() +53
  System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction) +28
  System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) +202
  System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) +414
  System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) +735868
  System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) +730199
  System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) +30
  System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1209
  System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
  System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
  System.Data.SqlClient.SqlConnection.Open() +111
  Truston.Managers.QuestionManager.LoadQuestionSetPoolItem(String controlID, String caseID, Int32 setID) in C:\Documents and Settings\Eric.MEWTWO\My Documents\Visual Studio 2005\Projects\Truston2005\Managers\QuestionManager.cs:159
  Truston.Managers.QuestionManager.ProcessLogic(String controlID, String caseID, Int32 questionID, String response, Boolean reverse) in C:\Documents and Settings\Eric.MEWTWO\My Documents\Visual Studio 2005\Projects\Truston2005\Managers\QuestionManager.cs:609
  Truston.Managers.QuestionManager.ProcessLogic(String controlID, String caseID, Int32 questionID, String response) in C:\Documents and Settings\Eric.MEWTWO\My Documents\Visual Studio 2005\Projects\Truston2005\Managers\QuestionManager.cs:636
  Truston.Managers.QuestionManager.ProcessResponse(String controlID, String caseID, Int32 questionID, String response) in C:\Documents and Settings\Eric.MEWTWO\My Documents\Visual Studio 2005\Projects\Truston2005\Managers\QuestionManager.cs:534
  test.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\TrustonWeb\test.aspx.cs:160
  System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
  System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
  System.Web.UI.Control.OnLoad(EventArgs e) +99
  System.Web.UI.Control.LoadRecursive() +47
  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Does anyone have information on this

Thanks.

Eric Valinsky, Principal Consultant
Plainwrap Solutions

Answer this question