Post reply

All About Transactions - Part 2

  • November 1, 2004 at 6:39 pm

    #76202

    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

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

  • November 25, 2004 at 6:29 am

    #531526

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

  • December 3, 2004 at 8:11 am

    #532459

    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.

  • December 9, 2004 at 8:05 pm

    #533277

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

    Thanks!

  • May 17, 2006 at 1:11 am

    #638352

    Really Great Article....

  • June 21, 2007 at 5:10 am

    #714302

    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!

  • June 21, 2007 at 5:17 am

    #714303

    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

  • June 11, 2010 at 7:10 am

    #1179140

    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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply