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

srini_r1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.