"mabster" wrote:

> Jim,
>
> Could this be related to the known issue where a ROLLBACK TRANSACTION on
> SQL Server 2000 rolls back ALL transactions, rather than just the most
> recent one?
>
> Check my blog post here:
>
> http://www.madprops.org/cs/blogs/mabster/archive/2005/10/31/2984.aspx
>
> Hope this helps,
> Matt
>

"JimM" <JimM@noemail.nospam> wrote in message news:118BEB1E-7B2D-41BF-897A-9DD436DBBA29@microsoft.com...
>I have the following code and the rollback is giving me an exception that
> there was no BEGIN TRANSACTION.  I have stepped through the code and the
> BeginTransaction is created.
>
> Now I am running a set of unit test forcing both failure and success
> scenarios.  All the test to this point are failures.  They all reported as
> expected.  The test prior to the exception is the first to execute the parent
> update with an original key that does not exist.  The transaction is rolled
> back and the exception is correctly reported back to the client.
>
> The next test trys changing the primary key.  The expected initial error is
> thrown at the ExecuteNonQuery statemetn on the parent.  The exception is "The
> query processor could not product a query plan from the optimizer because a
> query cannot update a text, ntext, or image column and the clustering key at
> the same time." The is the exception the unit test is expecting. but the
> Rollback statement faile with  "The ROLLBACK TRANSACTION request has no
> corresponding BEGIN TRANSACTION."
>
> My questions are:
>
> 1) Is the implementation of the transaction rollback valid in may code?
> 2) Why is the rollback failing with an exception when it did execute the
> "transaction = dataConnection.BeginTransaction( IsolationLevel.Serializable,
> transactionName );" statement creating a corresponding BEGIN TRANSACTION?  It
> is like the transaction was lost, corrupted, or not properly created.  But it
> work in the previous unit test.  I don't see what the error is. 
>
> Help!
>
> Example Code:
>
>  [WebMethod( Description = "Update the data in the SQL Database." )]
>  public void UpdateData( Data original, Data current )
>  {
>    sqlTransaction transaction;
>    const string transactionName = "UpdateData";
>    try
>    {
>    if( IsDisposed )
>    {
>      throw new ObjectDisposedException( this.ToString( ), ServiceNotRunning
> );
>    }
>
>    Authorization( ); // throws exception on authentication and
> authorization failures.
>
>    //----------------------------------------------------------------------
>    //  Validation
>    //----------------------------------------------------------------------
>    ArgumentCollection args = null; // ValidateData instantiates object if
> needed.
>    if( ! ( this.ValidateData( original, ref args ) &
>            this.ValidateData( current, ref args ) ) )
>    {
>      throw new ArgumentExceptions( ArgumentMessage, args );
>    }
>    //----------------------------------------------------------------------
>    //  Any changes made.
>    //----------------------------------------------------------------------
>    if( original == current )
>    {
>      throw new Exception( NoChangesMessage );
>    }
>    else
>    {
>      dataConnection.Open( ); // Open the SQLConnection object;
>      transaction = dataConnection.BeginTransaction(
> IsolationLevel.Serializable,
>                                                                           
> transactionName );
>
>      try // Try-Catch for SQL error processing
>      {
>        int countParentData = 0;
>        int countChildData = 0;
>
>        if( original.ParentDataChanged( current ) ) // did any parent fields
> change.
>        {             // Yes, update the parent;
>          updParentCommand.Connection = dataConnection;
>          updParentCommand.Transaction = transaction;
>
>          // The update sql parameters are set from the Data objects.
>
>          countParentData = updParentCommand.ExecuteNonQuery( );
>        }
>        else
>        {
>          countParentData = -1; // no changes in parent data.
>        }
>
>        if( original.ChileDataChanged( current ) )
>        {
>          updChildCommand.Connection = dataConnection;
>          updChildCommand.Transaction = transaction;
>
>          // The update sql parameters are set from the Data objects.
>
>          countChildData = updChildCommand.ExecuteNonQuery( );
>        }
>        else
>        {
>          countChildData = -1; // no changes in child data.
>        }
>
>       
> //---------------------------------------------------------------------
>        //  No error occurred check the counts to see if anything was done.
>       
> //---------------------------------------------------------------------
>        if( countParentData > 0 || countChildData > 0 )
>        {
>          transaction.Commit( );
>        }
>        else
>        {
>          // WHERE clause did not find the matching entry.
>          throw new Exception( NoMatchingEntry );
>        }
>      }
>      catch( SqlException ex )
>      {
>        transaction.Rollback( transactionName );
>        string message = "SQL Update transaction failure.  ";
>        if( ex.Class > 13 )
>        {
>          LogEvent( ex.ToString( ) );
>          message += EventLogged + DateTime.Now.ToString("F") + ReportTo;
>        }
>        else
>        {
>          message += ex.Message;
>        }
>        throw BuildSqlSoapException( message, ex );
>      }
>      catch( Exception ex )
>      {
>        transaction.Rollback( transactionName );
>        LogEvent( ex.ToString( ) );
>        throw BuildFatalSoapException( SoapException.ServerFaultCode, ex );
>      }
>      finally
>      {
>        dataConnection.Close( );
>      }
>    }
>  }
>  catch( SoapException )
>  {
>    throw;
>  }
>  catch( Exception ex )
>  {
>    throw BuildSoapException( ( ( ex is ArgumentException ) ? 
>                                             SoapException.ClientFaultCode :
>                                             SoapException.ServerFaultCode
> ), ex );
>
>  }
> }
>
>
> --
> Jim