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

More Information:

I tried removing the transactionName argument from the Rollback method. The
code works.

Why does the code fail when the transaction name is removed from the
Rollback method? The transaction name is the same one used in the
BeginTransaction method.

Interesting.
--
Jim

Hi Jim,

This is really weird. Did you use trasactions inside the update commands? I
suggest you try to start a trace using the SQL Profiler to see if the
trasaction has been started if the trasaction name has been provided. Also
you can check if it has been committed before.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Kevin,

There are no transaction statements within in the parent or child update SQL
stored procedures.

I ran the SQL Profiler.

Prior to the failed Update of the parent (the failure is expected as part of
unit test), there was an Insert for a parent and child row. This insert is
not the same record that is going to be updated in next test. These inserts
were within a Begin Transaction (transaction is not in Stored Procedure) and
the name of the Transaction was "AddData". The "Add" was successful so the
transaction was comitted.

The Update trace shows the following lines, note I'll use "" for an empty
column

Transaction ID, EventClass, EventSubClass, Text Data
"", SQL:StmtStarting, "", SET TRANACTION ISOLATION LEVEL SERIALIZABLE
"", SQL:StmtStarting, "", BEGIN TRANSACTION [UpdData]
99627, SQLTransaction, Begin, ""
"", Exception, "", Error: 8629, Severity: 16, State: 3 (This is the error
the unit test expected)
99627, SQLTransaction, Rollback, ""
"", SQL:StmtStarting,"",ROLLBACK TRANSACTION [UpdData]
"", Exception, "", Error: 3903, Severity: 16, State: 1 (This is the error I
am questioning, where the ROLLBACK fails)
"", SQL:StmtStarting, "", @out = @@transcount

At this point another unit tests is run and it succeeds, no rollback failure
on UpdData transaction.

The following is a trace without the transactionName in the rollback
statement.

Transaction ID, EventClass, EventSubClass, Text Data
"", SQL:StmtStarting, "", SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
"", SQL:StmtStarting, "", BEGIN TRANSACTION [UpdData]
104149, SQLTransaction, Begin, ""
"", Exception, "", Error: 8629, Severity: 16, State: 3 (Same as before )
104149, SQLTransaction, Rollback, ""
"", SQL:StmtStarting, "", IF @@TRANSCount > 0

Is there any additional information you need?

Regards,

Jim

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/mab...0/31/2984.aspx

Hope this helps,
Matt

Hi Matt,

Can you try to check the following KB article to see if this is the case?

http://support.microsoft.com/default...;en-us;Q309335

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Jim,

I ran into this issue using just SQL transactions within nested stored procedure calls. My solution was modified from a magazine article I read a few years back, so it's not all my own work (if anyone recognises it, thanks for the head start at the time). Solution is as follows:

First I've created a SQL function that returns whether a transaction is required, basically it checks the @@TRANCOUNT, returning 1 if it is zero or returning 0 for another value.

CREATE FUNCTION dbo.TransactionRequired()
RETURNS BIT
AS
BEGIN
DECLARE @TransactionRequired BIT
DECLARE @TranCount INT
SELECT @TranCount = IsNull( @@TRANCOUNT, 0)
IF( @TranCount = 0)
SET @TransactionRequired = 1
ELSE
SET @TransactionRequired = 0
RETURN @TransactionRequired
END
GO
This is then used in stored procedures in the following pattern:

CREATE dbo.rflsp_TransactionExample
@RollBackTran BIT = 0 OUTPUT
AS
BEGIN
DECLARE @TransactionRequired BIT
SELECT @TransactionRequired = dbo.TransactionRequired() <-- (1)

-- Perform any prelim. non-transaction reliant processing here.
IF( @TransactionRequired = 1) BEGIN TRAN <--(2)

-- Perform transaction reliant processing here.
IF(<<Fail Criteria Here>>)
BEGIN
IF( @TransactionRequired = 1) <--(3)
ROLLBACK TRAN
ELSE
SET @RollBackTran = 1
RETURN
END

IF( @TransactionRequired = 1) COMMIT TRAN <--(4)

-- Perform any remaining non-transaction reliant processing here.
END

It is worth noting that the inclusion of the @RollBackTran in participating stored procedures is crucial as this allows non transactional stored procedures to pass the transaction state through when required.

To run through the steps:

1) This checks the current @@TRANCOUNT using the previous described function. The variable @TransactionRequired will only be 1 if @@TRANCOUNT was found to be zero (i.e. no transaction was currently working.
2) This makes sure a transaction is only started if one is required, i.e. we are not already in one.
3) If the code should fail then we only rollback the transaction if this was the procedure that started it, otherwise set the output parameter @RollBackTran, so the parent transaction knows that it needs to either roll back the transaction (if it started it) or in turn pass the value of @RollBackTran back to its parent.
4) Commit the transaction if this was the procedure that started it.
Using the above pattern in all stored procedures makes sure you never suffer the ROLLBACK TRAN does not have a corresponding BEGIN TRAN error messsage.

Hope this helps,

- Paul.

"JimM" <(E-Mail Removed)> wrote in message news:118BEB1E-7B2D-41BF-897A-(E-Mail Removed)...
>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

I agree it looks like there has been a Rollback transaction that got the one
in my code, thus causing the exception. The only problem is the stored
procedure does not contain any transaction statements. The only BEGIN,
COMIT, and ROLLBACK statements is is the C# method. This problem only seems
to occur when the specific exception is "The query processor could not
produce a query plan form the optimizer because a query cannot update a text,
ntext, or image column and the clustering key at the same time." All other
error that can or are expected to occur do not fail with this issue.

I seem to have two choices, changed my code to bracket the ROLLBACK with a
try-catch block, where I ignore the ROLLBACK's exception and hope the
ROLLBACK on the update occured. The second is to use an un-named ROLLBACK.

Right now I am still using the named ROLLBACK, but I have change the stored
procedure to handle the changing of the clustering key and data as seperate
UPDATE statements. Thus avoiding the only exception where the ROLLBACK logic
fails because SQL Server stepped on the named transaction.

I am not planning on converting to SQL Server 2005, but I hope this issue is
resolved, where a ROLLBACK only rolls back one transaction and not the lot.
When I have time I'll try to build a test case that causes the problem and I
will try it against SQL 2000 and 2005.

Thanks for everyone suggestions and assistance.
--
Jim


"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/mab...0/31/2984.aspx
>
> Hope this helps,
> Matt
>