All About Transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Don Peterson » All About Transactions - Part 2

All About Transactions - Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

DCPeterson

Posted Monday, November 01, 2004 6:39 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 1,032, Visits: 390

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

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

Post #144214

sunjiulu

Posted Thursday, November 25, 2004 6:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, 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...

Post #148108

Vic Kirkpatrick-173212

Posted Friday, December 03, 2004 8:11 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, 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.

Post #149333

TroyG

Posted Thursday, December 09, 2004 8:05 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:08 PM
Points: 334, Visits: 389

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

Thanks!

Post #150346

Mohamed Manesh

Posted Wednesday, May 17, 2006 1:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 04, 2007 11:44 PM
Points: 1, Visits: 1

Really Great Article....

Post #280609

krzysztof-202150

Posted Thursday, June 21, 2007 5:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 08, 2011 2:40 AM
Points: 8, Visits: 104

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!

Post #375532

krzysztof-202150

Posted Thursday, June 21, 2007 5:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 08, 2011 2:40 AM
Points: 8, Visits: 104

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

Post #375533

yakov shlafman-228008

Posted Friday, June 11, 2010 7:10 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 05, 2010 2:07 PM
Points: 17, 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

Post #935981

« Prev Topic | Next Topic »

Permissions