TransactionScope and ROLLBACK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

TransactionScope and ROLLBACK

Author

Topic

sas_sqlteam
Starting Member

2 Posts

Posted - 2008-04-25 : 07:04:51

I have a stored procedure that can be executed either separately or as part of a bigger transaction that is created using class TransactionScope from a .NET application. First a small code snippet from the latter:

using (TransactionScope scope = new TransactionScope())
{
 using (SqlConnection cn = new SqlConnection(...))
 {
  using (SqlCommand cmd = cn.CreateCommand())
  {
   cmd.CommandText = "pTest";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.ExecuteNonQuery();
   scope.Complete();
  }
 }
}

Nothing special here. Now the stored procedure - exceedingly simple:

CREATE PROCEDURE pTest
AS
BEGIN
  BEGIN TRAN
  ROLLBACK TRAN
END

When this code is run I got the error message from SQL Server within ExecuteNonQuery:
The number of transactions after EXECUTE indicates that COMMIT or ROLLBACK TRANSACTION is missing. Previous number = 1, current number = 0.
(Sorry, this is a translation, the genuine English error message may be slightly different).

QUESTION #1: What does this message really mean?

As I see, rollback in the stored procedure does roll back the outermost transaction, and the server notices that for two "transaction starts" there's only one "end". But I NEED some way to rollback an inner transaction when there're errors. I tried the following trick:

ALTER PROCEDURE pTest
AS
BEGIN
  BEGIN TRAN
  IF @@TRANCOUNT = 1
  BEGIN
    ROLLBACK
    RETURN -1
  END
END

The idea was that if a procedure is run separately then @@TRANCOUNT will be 1 and it will be rolled back. If it's a part of a bigger transaction then @@TRANCOUNT > 1 and no rollback will occur, but we notify the caller that something bad and it must handle it.

Alas, now I get the following message:

The number of transactions after EXECUTE indicates that COMMIT or ROLLBACK TRANSACTION is missing. Previous number = 1, current number = 2.

Sure enough this is because there's neither COMMIT nor ROLLBACK in the inner transaction.

QUESTION #2: How can I roll back the inner transaction?

The only decision I came up with was the following:

ALTER PROCEDURE pTest
AS
BEGIN
  SET XACT_ABORT ON;
  BEGIN TRY
    BEGIN TRAN
      ... some statements ...
    COMMIT TRAN
  END TRY
  BEGIN CATCH
    RETURN -1
  END CATCH
END

There's no explicit ROLLBACK but in case of error XACT_ABORT ON will roll back entire transaction.
I don't like this approach and do not understand why it works (and whether it works

).

QUESTION #3: Are there better solutions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources