Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

All About Transactions - Part 2 Expand / Collapse
Author
Message
Posted Monday, November 01, 2004 6:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408
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
Posted Thursday, November 25, 2004 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, December 03, 2004 8:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Thursday, December 09, 2004 8:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 17, 2013 7:27 PM
Points: 340, Visits: 400

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

Thanks!

Post #150346
Posted Wednesday, May 17, 2006 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 04, 2007 11:44 PM
Points: 1, Visits: 1
Really Great Article....
Post #280609
Posted Thursday, June 21, 2007 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 9:34 AM
Points: 8, 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!
Post #375532
Posted Thursday, June 21, 2007 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 9:34 AM
Points: 8, 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
Post #375533
Posted Friday, June 11, 2010 7:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse