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)
 ROLLBACK

Author  Topic 

MichelleMA
Starting Member

32 Posts

Posted - 2006-11-09 : 15:25:06
Hi,
I am trying to use the ROLLBACK TRAN option in a SP, I am using it as follow, and I have this error :
Server: Msg 3902, Level 16, State 1, Procedure SP_Name, Line 57
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Can you please tell me what is wrong in my script?
Thanks,
Michelle
__________________________________________________

BEGIN TRANSACTION AddName
--Transaction Statement
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
END
COMMIT TRAN AddName

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 16:23:51
You need to put the commit in an else otherwise it runs even if the rollback runs and then you're trying to commit a transaction that's been rolled back.

BEGIN TRANSACTION AddName
--Transaction Statement
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
END
ELSE
BEGIN
COMMIT TRAN AddName
END
Go to Top of Page

MichelleMA
Starting Member

32 Posts

Posted - 2006-11-09 : 16:42:16
Thank You!,
One more question please:
If I have many statements in the SP, should I check @@Error and RollBack after each statement, and the same thing for the Else Commit statement?
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 16:51:15
After you start the transaction you must only actually execute one rollback or on commit, so you'll need to put in IF statements to check all along the way. Typically, if you get an error, you can rollback and then return (using the RETURN statement, because you don't want to exceute anything else in the sp.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 04:11:26
"should I check @@Error after each statement"

Afraid so, yes
Go to Top of Page

MichelleMA
Starting Member

32 Posts

Posted - 2006-11-10 : 10:06:44
Sean/Kristen Thank you,
Just to confirm, my code should look like this:
--STATEMENT1
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
ELSE
BEGIN
COMMIT TRAN AddName
END
--STATEMENT2
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
ELSE
BEGIN
COMMIT TRAN AddName
END
Go to Top of Page

CLages
Posting Yak Master

116 Posts

Posted - 2006-11-10 : 11:14:15
I am used to use a Flag

Declare @flag
set @flag = 0

BEGIN TRANSACTION AddName

--First Transaction Statement
IF @@ERROR <>0
BEGIN
set @flag = @flag + 1
END

--Second Transaction Statement
IF @@ERROR <>0
BEGIN
set @flag = @flag + 1
END


IF @flag > 0 -- I get some Error
BEGIN
ROLLBACK TRAN AddName
END
ELSE
BEGIN
COMMIT TRAN AddName
END


C. Lages
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-10 : 13:28:04
quote:
Originally posted by MichelleMA

Sean/Kristen Thank you,
Just to confirm, my code should look like this:
--STATEMENT1
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
ELSE
BEGIN
COMMIT TRAN AddName
END
--STATEMENT2
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
ELSE
BEGIN
COMMIT TRAN AddName
END


This is incorrect, because you wouldn't want to commit after statement 1, so you only want one commit at the end. Like this

--STATEMENT1
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
--STATEMENT2
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
ELSE
BEGIN
COMMIT TRAN AddName
END

In fact because you now have returns in there, you wouldn't need to have the commit in the else, because the code will never reach the commit if one of the errors occurs, so this will be fine

--STATEMENT1
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
--STATEMENT2
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN AddName
RETURN
END
COMMIT TRAN AddName
Go to Top of Page

MichelleMA
Starting Member

32 Posts

Posted - 2006-11-10 : 15:29:32
Hi All,
I tried both methods using the RETURN and the Flag, they both work, and I can see in the Query Analyzer's results screen all the transactions that executed and which disappeared after the rollback

THANK YOU ALL FOR YOUR HELP!

Michelle
Go to Top of Page
   

- Advertisement -