August 7, 2013 at 1:18 pm
Hello All,
I have a question behind the logic of Try and Catch. I'm using Try Catch in a complex statement with a While Loop. If I put the Try . . . Catch outside of the while loop and say the loop fails the 3rd time around, will the entire statement be caught and no transaction committed? By this I mean will the previous two loops be negated? I'm assuming that this is the case.
Thanks again!
August 7, 2013 at 1:43 pm
JoshDBGuy (8/7/2013)
Hello All,I have a question behind the logic of Try and Catch. I'm using Try Catch in a complex statement with a While Loop. If I put the Try . . . Catch outside of the while loop and say the loop fails the 3rd time around, will the entire statement be caught and no transaction committed? By this I mean will the previous two loops be negated? I'm assuming that this is the case.
Thanks again!
That depends. Do you do a new transaction each loop iteration or is the transaction outside of the loop?
Given that you worrying about transactions within a loop...I suspect it would improve your process greatly to get rid of the loop entirely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 2:32 pm
Sean Lange (8/7/2013)
JoshDBGuy (8/7/2013)
Hello All,I have a question behind the logic of Try and Catch. I'm using Try Catch in a complex statement with a While Loop. If I put the Try . . . Catch outside of the while loop and say the loop fails the 3rd time around, will the entire statement be caught and no transaction committed? By this I mean will the previous two loops be negated? I'm assuming that this is the case.
Thanks again!
That depends. Do you do a new transaction each loop iteration or is the transaction outside of the loop?
Given that you worrying about transactions within a loop...I suspect it would improve your process greatly to get rid of the loop entirely.
The transactions are inside the loop. I would use a fetch or something similar instead but I'm attempting to move millions of records from multiple tables and I'm looping with batches to do this.
August 7, 2013 at 2:40 pm
Try...Catch... is not a transaction controlling statement. It's more of an error handling in SQL Server. If you do not have a transaction block anywhere, every successful update will be committed, except the last one that failed.
Happy Coding!!!
~~ CK
August 7, 2013 at 2:44 pm
Within the loop there are multiple transaction statements. Because these are all very dependent on one another i'd like the loop to stop completely and not insert or delete any records if there is a failure at any point. I could probably split up each transaction into separate loops but I was hoping there's another way to go about this.
August 7, 2013 at 2:58 pm
JoshDBGuy (8/7/2013)
Within the loop there are multiple transaction statements. Because these are all very dependent on one another i'd like the loop to stop completely and not insert or delete any records if there is a failure at any point. I could probably split up each transaction into separate loops but I was hoping there's another way to go about this.
If you want some help making your entire process extremely solid and fast I would be willing to help you rework it without the loops. I am guessing you are doing some sort of CRUD inside your loop. Loops in sql are horrible for performance and should only be used in a few situations.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 3:08 pm
Sean Lange (8/7/2013)
JoshDBGuy (8/7/2013)
Within the loop there are multiple transaction statements. Because these are all very dependent on one another i'd like the loop to stop completely and not insert or delete any records if there is a failure at any point. I could probably split up each transaction into separate loops but I was hoping there's another way to go about this.If you want some help making your entire process extremely solid and fast I would be willing to help you rework it without the loops. I am guessing you are doing some sort of CRUD inside your loop. Loops in sql are horrible for performance and should only be used in a few situations.
Well right now it's about 600 lines or so of code. Maybe a pointer in the right direction. If I want to migrate millions of rows (all of which are dependent on each other so one move can't break the process) what's the best way to go about it? I didn't use a cursor as I believe that will be even slower although it would probably be safer.
Because this is for archiving purposes, the best way to take care of this would be to partition the tables. However, my company doesn't like that idea because of the perceived maintenance issue in case I leave the company or I go on vacation. I think it's a bit hard for people to understand who haven't used it in the past (although I think it's pretty easy).
August 7, 2013 at 3:59 pm
If you start a single transaction before the loop begins (or, more awkwardly, only on the first loop), then any rollback will rollback all activity for that transaction. If I understand you correctly, that's what you want anyway.
If you did want to commit data at points during the loop, you'd have to issue a COMMIT and then a (new) BEGIN TRAN. Obviously once you commit or rollback a given tran you can't reverse that later :-D.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply