September 23, 2008 at 12:12 pm
I need to run a script to correct an error that was made during a data import. In part, it requires swapping out sequential dates - so I've decided to use a cursor to do this. I know, I know - don't give me grief about that - it's a small table with only 14K records.
But here is my question. If the script fails in the middle, I absolutely MUST rollback the entire thing and start over to maintain my data integrity.
Can you show me what the transaction code should look like for this? And is it possible? Can the entire cursor from beginning to end be a single transaction that can be rolled back? Or is every pass of the cursor a transaction?
[font="Courier New"]ZenDada[/font]
September 23, 2008 at 12:20 pm
If you define an explicit transaction, and do the appropriate error handling, you can roll the whole lot back. I suggest using Try..Catch as @@error requires a check after every single operation that can fail, and it gets messy fast.
DECLARE Cursor...
open cursor
fetch ..
BEGIN TRANSACTION
BEGIN TRY
WHILE @@fetch_status
BEGIN
.. all of your operations here
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- any other logiing or cleanup
END CATCH
IF @@TranCount>0 -- Transaction still open, so must have succeeded. If rolled back, trancount would be 0
COMMIT TRANSACTION
CLOSE cursor
DEALLOCATE cursor
Edit: Left out the commit.
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
September 23, 2008 at 12:25 pm
Sweet! That is exactly what I needed!
[font="Courier New"]ZenDada[/font]
September 23, 2008 at 12:57 pm
Pleasure
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply