Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


All About Transactions - Part 2


All About Transactions - Part 2

Author
Message
DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 432
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpet

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
sunjiulu
sunjiulu
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 35

Part 2 is as good as part one, thanks.

one question:

what severity level error can be handled by @@error? below 17? and what is the example for severity level 16? I try to find out from book online, no luck. Have to test all scenario about constraint violation. e.g. null, duplicated key...





Vic Kirkpatrick-173212
Vic Kirkpatrick-173212
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 76
Well, well, well. A nicely written article that makes tons of sense. Recently I have had to write some complex T-SQL conversion scripts with multiple batches and a lot of logic. Getting the bugs worked out of them once written was tough to do and would have been a lot tougher without proper error handling and knowing about the limitations of XACT_ABORT. Eventually I ended up turning it off like the author suggested and using a lot of @@error logic, and yes like Don said you unfortunately have to work out all the kinks during testing because the transactions are aborted when using batches whenever the first error occurs. Can't wait for the try-catch stuff in 2005.
TroyG
TroyG
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 504

brilliant article - flows nicely from pt 1....and now onto pt 3....

Thanks!


Mohamed Manesh
Mohamed Manesh
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Really Great Article....
krzysztof-202150
krzysztof-202150
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 123
I really wish I had come across your series of Articles before.

But well, even though there were published almost 3 years ago they are great and it is never too late to learn good things.

The Articles are great, written in the way that says: "Just take some time to go through examples and you will see how easy it is"

One can tell straight away that you know exactly what you are talking about and that you really like it, and what is more that you want other people to understand it and like it as well.

Thanks for that, I really appreciate it!
krzysztof-202150
krzysztof-202150
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 123
As going through the examples keeps your mind active I just came with the idea of an alternative style of error handling.
One that combines the two mentioned in the Article:

It avoids using GO TO and it allows the use of the copy and paste approach:

DECLARE @myErr TABLE(err int)
BEGIN TRAN
INSERT INTO City SELECT 'Anchorage', 'AK'
INSERT INTO @myErr SELECT @@ERROR
INSERT INTO City SELECT 'Los Angles', 'CA'
INSERT INTO @myErr SELECT @@ERROR
INSERT INTO City SELECT 'San Juan', 'PR' --Puerto Rico isn't in our State table
INSERT INTO @myErr SELECT @@ERROR
INSERT INTO City SELECT 'Topeka', 'KS'
INSERT INTO @myErr SELECT @@ERROR
IF (SELECT MAX(err) FROM @myErr) = 0
COMMIT
ELSE
ROLLBACK
yakov shlafman-228008
yakov shlafman-228008
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 281
Hi Don, thanks for your articles about transaction.
I think your articles are the best on this topic.

Don, you wrote "Hopefully it is now obvious that if you turn on XA, you also eliminate your ability to trap and respond to runtime errors". I am not sure that this is correct statement. I think you could handle/trap runtime errors when XA is on.
Please advise.
Do you have any other written materials on transactions, nested transactions?
thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search