June 15, 2011 at 10:00 pm
Hi all, I am having a trouble to generate a single transaction with multiple alter stored procedure statements:
1. Have 40+ stored procedures need to update
2. Want to build a single transaction with error rollback function, so that either all success or all fail.
Problem:
1. If I use @@error for each alter statement, how could I jump uot of the whole transaction? And how could I rollback all? I tried goto, but the next alter statement will throw error that alter must be the first statement.
2. If I use try catch box for whole transaction, again have problem with alter statement....
Any good suggestions? Or example that could solve this problem? Or how usually handle this request? Thanks a lot:)
June 16, 2011 at 5:32 am
Look into BEGIN TRANSACTION and END TRANSACTION. You'll probably want to name it. Try using the "new" TRY - CATCH functionality for error trapping.
You can find information on both of these in Books Online. I don't know, however, if this will work with altering stored procedures. I suppose if you don't use GO statements, it should.
June 16, 2011 at 6:55 am
see opc.three's post from the thread below;
i learned something here, since i was used to think that GO + BEGIN TRANS were not compatible, and now i know better:
http://www.sqlservercentral.com/Forums/Topic1125836-391-1.aspx#bm1125844
Lowell
June 16, 2011 at 7:13 am
Lowell (6/16/2011)
see opc.three's post from the thread below;i learned something here, since i was used to think that GO + BEGIN TRANS were not compatible, and now i know better:
http://www.sqlservercentral.com/Forums/Topic1125836-391-1.aspx#bm1125844
Thanks Lowell, I looked into the link and I think his question is the same as mine, so u suggest to use dynamic SQL? Wich will change a lot of my current scripts. Did the other response work?
June 16, 2011 at 7:17 am
princa (6/16/2011)
Lowell (6/16/2011)
see opc.three's post from the thread below;i learned something here, since i was used to think that GO + BEGIN TRANS were not compatible, and now i know better:
http://www.sqlservercentral.com/Forums/Topic1125836-391-1.aspx#bm1125844
Thanks Lowell, I looked into the link and I think his question is the same as mine, so u suggest to use dynamic SQL?
Actually, opc.three's last comment says dynamic SQL is NOT necessary. Read a little closer.
June 16, 2011 at 7:19 am
Lowell (6/16/2011)
see opc.three's post from the thread below;i learned something here, since i was used to think that GO + BEGIN TRANS were not compatible, and now i know better:
http://www.sqlservercentral.com/Forums/Topic1125836-391-1.aspx#bm1125844
Not quite what I was thinking, Lowell, though I thank you for the link.
My assumption is the TRY...CATCH is not compatible with the GO statements.
June 16, 2011 at 7:20 am
Brandie Tarvin (6/16/2011)
princa (6/16/2011)
Lowell (6/16/2011)
see opc.three's post from the thread below;i learned something here, since i was used to think that GO + BEGIN TRANS were not compatible, and now i know better:
http://www.sqlservercentral.com/Forums/Topic1125836-391-1.aspx#bm1125844
Thanks Lowell, I looked into the link and I think his question is the same as mine, so u suggest to use dynamic SQL?
Actually, opc.three's last comment says dynamic SQL is NOT necessary. Read a little closer.
Hi BT, yes I read the response thanks, I think it,s not necessary too, way too complicated. I just looked into xact_abort, I think it's exactly the case for me, I will try now and see if it works. Thanks a lot!!
June 16, 2011 at 7:21 am
yes, opc.three's comment is the key; i testest it to make sure...for me i created a suite of tables and then 4 different functions and procs, all with GO statements in bewtween; when there was an error, the whole thing rolled back,a nd when ti was clean/error free, it committed the whole thing.
after i thought about it, it made perfect sense, since that's really what any application does ...it starts a transaction, and then makes multiple command calls...sometimes with differnet connections.... ithat post helped me see the error of my older way of thinking.
Lowell
June 16, 2011 at 7:35 am
Lowell (6/16/2011)
yes, opc.three's comment is the key; i testest it to make sure...for me i created a suite of tables and then 4 different functions and procs, all with GO statements in bewtween; when there was an error, the whole thing rolled back,a nd when ti was clean/error free, it committed the whole thing.after i thought about it, it made perfect sense, since that's really what any application does ...it starts a transaction, and then makes multiple command calls...sometimes with differnet connections.... ithat post helped me see the error of my older way of thinking.
Yes, Lowell and BT, I tried this way and it works fine if there is no error in the whole transaction and commit every changes.
However, if there is an error, it does roll back all the transaction, but with a below error message:
"Msg 208, Level 16, State 1, Procedure vwExistingActions, Line 3
Invalid object name 'xxxxx'. ---This is the actual error I made it.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
My structure:
Set xact_abort ON
Begin Transaction
GO
alter procedure....
GO
alter procedure...
GO
Commit Transaction
GO
So when it rolls back, does the error message makes sense? (no corresponding begin transaction). should I name the trasaction?!:D
June 16, 2011 at 8:01 am
Try naming the Transaction. See if that gets around the error.
Also, what you wrote makes no sense. You have a transaction with no way of backing out if there's an error. Don't ever write a script with BEGIN...COMMIT and no ROLLBACK. That's just sloppy coding.
What happened to the request you had in your original post about tracking errors? Did you not code that?
June 16, 2011 at 8:04 am
Lowell (6/16/2011)
yes, opc.three's comment is the key; i testest it to make sure...for me i created a suite of tables and then 4 different functions and procs, all with GO statements in bewtween; when there was an error, the whole thing rolled back,a nd when ti was clean/error free, it committed the whole thing.after i thought about it, it made perfect sense, since that's really what any application does ...it starts a transaction, and then makes multiple command calls...sometimes with differnet connections.... ithat post helped me see the error of my older way of thinking.
I tested again and I found out that even I set xact_abort ON, it doesn't roll back the whole transaction, it rolls back any statements before the error, but after all run....that's why have another error saying "no corresponding begin transaction for commit transaction", because it rolls back everything and corresponding to the begin transaction, and then it goes to the end of script then hit commit trasaction and there is not corresponding one...
so set xact_abort ON will really roll back everything in a single transaction?
June 16, 2011 at 8:07 am
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
The Xact_abort rolls back everything and aborts the *batch*, however each piece broken with GO is a separate batch, so execution simply resumes at the beginning of the next batch
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 8:08 am
Brandie Tarvin (6/16/2011)
Try naming the Transaction. See if that gets around the error.
Naming transactions does just about nothing. It's a documentation thing (with one minor exception)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 8:15 am
GilaMonster (6/16/2011)
Brandie Tarvin (6/16/2011)
Try naming the Transaction. See if that gets around the error.Naming transactions does just about nothing. It's a documentation thing (with one minor exception)
I admit. That was just a W.A.G. on my part. :blush:
June 16, 2011 at 8:20 am
princa (6/16/2011)
Lowell (6/16/2011)
yes, opc.three's comment is the key; i testest it to make sure...for me i created a suite of tables and then 4 different functions and procs, all with GO statements in bewtween; when there was an error, the whole thing rolled back,a nd when ti was clean/error free, it committed the whole thing.after i thought about it, it made perfect sense, since that's really what any application does ...it starts a transaction, and then makes multiple command calls...sometimes with differnet connections.... ithat post helped me see the error of my older way of thinking.
I tested again and I found out that even I set xact_abort ON, it doesn't roll back the whole transaction, it rolls back any statements before the error, but after all run....that's why have another error saying "no corresponding begin transaction for commit transaction", because it rolls back everything and corresponding to the begin transaction, and then it goes to the end of script then hit commit trasaction and there is not corresponding one...
so set xact_abort ON will really roll back everything in a single transaction?
Thanks GilaMonster, that's a problem if I have tons of GO statement which create tons of batches....your statement is exactly accurate, it only rolls back from beginning until the error batch...not the following batches...
Any suggestions on this situation if you have lots of alter statements which need GO syntax? Or other ways to alter stored procedure?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply