SQLServerCentral / Discuss Content Posted by Don Peterson / Article Discussions / Article Discussions by Author / All About Transactions - Part 2 / Latest Posts

RE: All About Transactions - Part 2

yakov shlafman-228008 — Fri, 11 Jun 2010 07:10:20 GMT

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

RE: All About Transactions - Part 2

krzysztof-202150 — Thu, 21 Jun 2007 05:17:00 GMT

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

RE: All About Transactions - Part 2

krzysztof-202150 — Thu, 21 Jun 2007 05:10:00 GMT

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!

RE: All About Transactions - Part 2

Mohamed Manesh — Wed, 17 May 2006 01:11:00 GMT

Really Great Article....

RE: All About Transactions - Part 2

TroyG — Thu, 09 Dec 2004 20:05:00 GMT

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

Thanks!

RE: All About Transactions - Part 2

Vic Kirkpatrick-173212 — Fri, 03 Dec 2004 08:11:00 GMT

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.

RE: All About Transactions - Part 2

sunjiulu — Thu, 25 Nov 2004 06:29:00 GMT

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...

All About Transactions - Part 2

DCPeterson — Mon, 01 Nov 2004 18:39:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpet