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 57The 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 StatementIF @@ERROR <>0BEGIN ROLLBACK TRAN AddNameENDCOMMIT 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 StatementIF @@ERROR <>0BEGIN ROLLBACK TRAN AddNameENDELSEBEGIN COMMIT TRAN AddNameEND |
|
|
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? |
|
|
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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-10 : 04:11:26
|
"should I check @@Error after each statement"Afraid so, yes |
|
|
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:--STATEMENT1IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END ELSE BEGIN COMMIT TRAN AddName END--STATEMENT2IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END ELSE BEGIN COMMIT TRAN AddName END |
|
|
CLages
Posting Yak Master
116 Posts |
Posted - 2006-11-10 : 11:14:15
|
I am used to use a FlagDeclare @flag set @flag = 0BEGIN TRANSACTION AddName--First Transaction Statement IF @@ERROR <>0BEGIN set @flag = @flag + 1END--Second Transaction Statement IF @@ERROR <>0BEGIN set @flag = @flag + 1ENDIF @flag > 0 -- I get some ErrorBEGIN ROLLBACK TRAN AddNameENDELSEBEGIN COMMIT TRAN AddNameENDC. Lages |
|
|
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:--STATEMENT1IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END ELSE BEGIN COMMIT TRAN AddName END--STATEMENT2IF @@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--STATEMENT1IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END--STATEMENT2IF @@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--STATEMENT1IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END--STATEMENT2IF @@ERROR <>0 BEGIN ROLLBACK TRAN AddName RETURN END COMMIT TRAN AddName |
|
|
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 rollbackTHANK YOU ALL FOR YOUR HELP!Michelle |
|
|
|