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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 ROLLBACK
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MichelleMA
Starting Member

USA
32 Posts

Posted - 11/09/2006 :  15:25:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/09/2006 :  16:23:51  Show Profile  Reply with Quote
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

USA
32 Posts

Posted - 11/09/2006 :  16:42:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/09/2006 :  16:51:15  Show Profile  Reply with Quote
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

United Kingdom
22859 Posts

Posted - 11/10/2006 :  04:11:26  Show Profile  Reply with Quote
"should I check @@Error after each statement"

Afraid so, yes
Go to Top of Page

MichelleMA
Starting Member

USA
32 Posts

Posted - 11/10/2006 :  10:06:44  Show Profile  Reply with Quote
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

Brazil
116 Posts

Posted - 11/10/2006 :  11:14:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/10/2006 :  13:28:04  Show Profile  Reply with Quote
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

USA
32 Posts

Posted - 11/10/2006 :  15:29:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000