Nested transaction throws error

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 2000 Forums

SQL Server Development (2000)

Nested transaction throws error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-23 : 08:38:13

Jayasimha writes "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 13
Cannot roll back scr. No transaction or savepoint of that name was found.

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

/*
If COMMIT TRAN scr is compulsory in scores procedure, please modify accordingly using a simple IF condition and test and still it doesn't work.*/"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-23 : 08:41:49

Sorry, we don't answer homework questions on SQL Team. You won't learn anything if we did.

I'd suggest looking in Books Online under "Transactions". They are explained very nicely and there are several examples.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources