SET XACT_ABORT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SET XACT_ABORT

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-07 : 11:47:41

I Have a very complex S.P. that does many updates to many tables.
If this encounters an error anywhere after the first couple of statents I want to rollback all of SP After those 1st couple of staments .

Will inserting (SET XACT_ABORT ON) at my "break Point" accomplish this? BOL Is a little criptic on SET XACT_ABORT.

Is there a better Way?

Jim
Users <> Logic

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-07 : 12:29:59

Is there a reason that:

BEGIN TRANSACTION

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

won't work?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-07 : 12:38:37

Derrick

To be Honest I have never used ROLLBACK TRANSACTION in any of My SP's. (I usually make the front end so that an SP error Cannot occure.)

Guess I better Start, These are getting way to complex, If it fails along the way I have a mess to clean up.

I ran Accross SET XACT_ABORT ON and wondered if it would work.

Jim
Users <> Logic

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-07 : 12:44:16

You really should use ROLLBACK TRANSACTION. :) This is especially true on updates. A lot of times updates won't fail to a point they cause an error. They still haven't worked though. This requires testing and setting the ROLLBACK accordingly. Does that make sense? Either way, the use of transactions and RAISERROR are a good thing to know.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-05-08 : 09:59:52

quote:
I Have a very complex S.P. that does many updates to many tables.
If this encounters an error anywhere after the first couple of statents I want to rollback all of SP After those 1st couple of staments .

Will inserting (SET XACT_ABORT ON) at my "break Point" accomplish this? BOL Is a little criptic on SET XACT_ABORT.

We use SET XACT_ABORT ON in all our SProcs so that we can be sloppy with our IF @@ERROR <> 0 checks.

Not 100% sure, but I think that even with "SET XACT_ABORT ON" if SProc_A calls SProc_B, and SProc_B has [say] a syntax error in it then SProc_A will NOT automatically die - so at the lease you still need to do an IF @@ERROR <> 0 check after each EXEC of a nested SProc.

We still use BEGIN TRANSACTION whenever an SProc updates the DB, and do either ROLLBACK or COMMIT based on the occurence of any soft errors (e.g. "This should never happen" type checks)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources