Cannot roll back a transaction in a nested transaction
Posted on 2004-11-20
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
@myAvg smallint OUTPUT
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
CREATE PROCEDURE stest
@Avg smallint OUTPUT
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
COMMIT TRAN st
DECLARE @res INT
EXEC stest 40, 20, 30, @res OUTPUT