July 13, 2006 at 7:10 pm
Until now, all of the things I've done in SQL would not be hard to fix if there was a power outage during an INSERT or UPDATE.
I'm working on something that the last procedure updates 20 different tables with data. About a three to five second procedure. My fear is the procedure starts, a power outage, etc and now I have some tables updated and some not.
I think there is something called ROLLBACK. How does it work and would this be something that would work for me?
Thanks!!
July 13, 2006 at 7:39 pm
see about set xact_abort on ,
have your store procedure start with begin tran ... end tran ?
July 13, 2006 at 9:12 pm
I'm not sure I understand. Lets say this is my stored procedure. If I want this all to run before updating the database, what do I need to do. Thanks!
INSERT INTO TableA
(Name, Address, State)
SELECT
'Ed', '111', ' '
UPDATE TableA
SET State = 'TX'
EXEC p_SomeOtherProc
INSERT INTO TableB
(Name, Address, State)
SELECT
'Ed', '111', ' '
UPDATE TableB
SET State = 'TX'
INSERT INTO Table
(Name, Address, State)
SELECT
'Ed', '111', ' '
UPDATE TableC
SET State = 'TX'
July 14, 2006 at 12:00 am
Begin Tran ChooseAName
Set @Error=0
update
set [field]=value
Set @Error=@Error+@@error
delete from
where [condition]
Set @Error=@Error+@@error
--keep store the last @error value, if there is no error the value of @@error will be zero (so you can commit your transaction)
If @Error=0
Commit Tran ChooseAName
Else
Rollback Tran ChooseAName
Hope this help.
Claudia
July 14, 2006 at 2:17 am
The alternative as mentioned earlier is to use xact_abort
SET xact_abort ON
BEGIN TRANSACTION
----Do your stuff here
COMMIT TRANSACTION
SET xact_abort OFF
xact_abort defaults to OFF so if an error occours inside a transaction it does not get aborted, execution continues with the next statement and the transaction gets comitted at the end of the batch. By setting it to ON when an error is encountered the transaction is automatically rolled back.
July 14, 2006 at 4:37 am
Thanks for the solutions. Since there are two different solutions, the obvious question is which best fits my concern.
I'm still a little confused on which fits my situation the best. The xact_abort is the easiest to code. I searched this forum and came up with this article.
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart2.asp
My first concern is the server going down mid-transaction. It looks like either method will work fine.
It seems like both methods will catch an INSERT or an UPDATE that fails.
What other things should I be thinking about to make this decision? Thanks again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply