• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

Cannot roll back a transaction in a nested transaction

I have a procedure stest having BEGIN TRAN which calls another procedure scores which also has a BEGIN TRAN in it. When i call stest using EXEC stest, i get the following errors:

Server: Msg 6401, Level 16, State 1, Procedure scores, Line 12
Cannot roll back scr. No transaction or savepoint of that name was found.
Server: Msg 266, Level 16, State 2, Procedure scores, Line 13
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

What is the problem ?

Below i am pasting the code for both the procedures and calling the first procedure. Just paste into query analyser and run.

CREATE PROCEDURE scores
@score1 smallint,
@score2 smallint,
@score3 smallint,
@myAvg smallint OUTPUT

AS
PRINT 'TRAN COUNT before scr: ' + CAST(@@TRANCOUNT AS CHAR)
BEGIN TRAN scr
PRINT 'TRAN COUNT after scr: ' + CAST(@@TRANCOUNT AS CHAR)
SELECT @myAvg = (@score1 + @score2 + @score3 )/3
ROLLBACK TRAN scr
RETURN
GO

/***************/

CREATE PROCEDURE stest
@sc1 smallint,
@sc2 smallint,
@sc3 smallint,
@Avg smallint OUTPUT

AS
PRINT 'TRAN COUNT before st: ' + CAST(@@TRANCOUNT AS CHAR)
BEGIN TRAN st
PRINT 'TRAN COUNT after st: ' + CAST(@@TRANCOUNT AS CHAR)
EXEC scores @sc1, @sc2, @sc3, @Avg OUTPUT
SELECT @Avg
COMMIT TRAN st
GO

/**************/

BEGIN
DECLARE @res INT
EXEC stest 40, 20, 30, @res OUTPUT
SELECT @res
END

0
srini_r1
Asked:
srini_r1
  • 2
  • 2
1 Solution
 
srini_r1Author Commented:
Please reply at the earliest possible
0
 
KarinLoosCommented:
Firstly you dont have a commit transaction in procedure scores.
Secondly this procedure does nothing besides calculate an amount on the basis of incoming parameters, there are no tables involved with insert/update/delete statements so absolutely no need for a transaction.
Thirdly your main proc also does nothing besides returning a result from the other proc , so again why the transaction, what are you committing?  the setting of variables ?
Thirdly, if this procedure did do something (inserts/updates/deletes)  then only include begin trans in  the outer ie calling procedure.

0
 
srini_r1Author Commented:
Sorry..can't accept.
I created tables and included UPDATE statement and tried to ROLLBACK. Still it throws same error. (I am using SQL Server 2000)

Copy the below code in Query Analyzer and run (Table creation also include in the beginning.)

CREATE TABLE DEMOTABLE(VAL INTEGER)
INSERT INTO DEMOTABLE(VAL) Values(5000)

/*********/

CREATE PROCEDURE scores
@score1 INT,
@score2 INT,
@score3 INT,
@myAvg INT OUTPUT

AS
BEGIN TRAN scr
  SELECT @myAvg = (@score1 + @score2 + @score3 )/3
  UPDATE DEMOTABLE SET VAL = @myAvg WHERE VAL = 5000
  SELECT @myAvg = VAL FROM DEMOTABLE
  PRINT 'VAL after update = ' + CAST(@myAvg AS CHAR)
ROLLBACK TRAN scr
GO

/*********/

CREATE PROCEDURE stest
@sc1 INT,
@sc2 INT,
@sc3 INT,
@myAvg INT OUTPUT

AS
BEGIN TRAN st
  EXEC scores @sc1, @sc2, @sc3, @myAvg OUTPUT
  UPDATE DEMOTABLE SET VAL = 5000 WHERE VAL = @myAvg
COMMIT TRAN st
GO

/*********/

BEGIN
DECLARE @res INT
EXEC stest 40, 20, 30, @res OUTPUT
SELECT @res
PRINT '@@Trancount = ' +CAST(@@TRANCOUNT AS CHAR)
END

0
 
KarinLoosCommented:
BOL says:
Naming multiple transactions in a series of nested transactions with a transaction name
has little effect on the transaction. Only the first (outermost) transaction name is registered with the system.
A rollback to any other name (other than a valid savepoint name) generates an error.
None of the statements executed before the rollback are in fact rolled back at the time this error occurs.
The statements are rolled back only when the outer transaction is rolled back.

as i said in my first post ONLY put the transaction in the OUTER procedure ie

CREATE PROCEDURE scores
@score1 INT,
@score2 INT,
@score3 INT,
@myAvg INT OUTPUT

AS
  SELECT @myAvg = (@score1 + @score2 + @score3 )/3
  UPDATE DEMOTABLE SET VAL = @myAvg WHERE VAL = 5000
  SELECT @myAvg = VAL FROM DEMOTABLE
  PRINT 'VAL after update = ' + CAST(@myAvg AS CHAR)
GO

/*********/


CREATE PROCEDURE stest
@sc1 INT,
@sc2 INT,
@sc3 INT,
@myAvg INT OUTPUT

AS
BEGIN TRAN
  EXEC scores @sc1, @sc2, @sc3, @myAvg OUTPUT
  UPDATE DEMOTABLE SET VAL = 5000 WHERE VAL = @myAvg
COMMIT TRAN
GO

/*********/

BEGIN
DECLARE @res INT
EXEC stest 40, 20, 30, @res OUTPUT
SELECT @res
PRINT '@@Trancount = ' +CAST(@@TRANCOUNT AS CHAR)
END
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now