July 1, 2014 at 4:02 pm
I have a big script that in a loop does some processing. After it encounters an error, it exits the loop. I need it to resume execution.
July 1, 2014 at 4:08 pm
Have you used TRY...CATCH to handle errors?
July 2, 2014 at 7:46 am
Yes I tried TRY/CATCH:
BEGIN TRY
< ...my code...>
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
CONTINUE
END CATCH
But after the 1st error, for each loop iteration, it gives a message "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
July 2, 2014 at 8:06 am
You will have to rollback the transaction in the CATCH block.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 2, 2014 at 3:35 pm
If you want to keep/commit previous activities in the loop, you likely need to explicitly BEGIN and COMMIT a tran for each loop, something like this:
WHILE ...
BEGIN
BEGIN TRANSACTION
...
BEGIN TRY
...
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
CONTINUE
END CATCH
...
COMMIT TRANSACTION
END ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply