Correct placement of SET XACT_ABORT ON

Correct placement of SET XACT_ABORT ON

Post by Rod » Sat, 23 Aug 2003 19:16:45



One of my developers has used the T-SQL command SET XACT_ABORT ON (which I
confess I was unfamiliar with) in his stored procedures. I have reason to
believe that the transaction is failing, for one reason or another, to store
the data. In looking at his code he had a statement like this:

BEGIN TRANSACTION
SET XACT_ABORT ON

However, in looking at the online help, they had the sequence of statements
like this:

SET XACT_ABORT ON
BEGIN TRANSACTION

My question is, does it matter?

Rod

 
 
 

Correct placement of SET XACT_ABORT ON

Post by Scott Morri » Sat, 23 Aug 2003 20:23:42


Apparently, it does not matter (at least in v7).  Try the following as is
and notice that the batch continues to the end.  Then uncomment one of the
xact_abort lines, run , etc.  Regardless of placement, the batch aborts at
the 2nd insert.

SET ANSI_WARNINGS ON
go
IF OBJECT_ID('TEST') is not null DROP TABLE TEST
GO

CREATE TABLE TEST ( CTEST CHAR(10))
GO

SET XACT_ABORT OFF
--SET XACT_ABORT ON
BEGIN TRANSACTION
--SET XACT_ABORT ON
INSERT TEST (CTEST) VALUES ( '0123456789')
INSERT TEST (CTEST) VALUES ( '01234567890')
SELECT 'CONTINUE'
ROLLBACK TRAN
GO

IF OBJECT_ID('TEST') is not null DROP TABLE TEST
GO


Quote:> One of my developers has used the T-SQL command SET XACT_ABORT ON (which I
> confess I was unfamiliar with) in his stored procedures. I have reason to
> believe that the transaction is failing, for one reason or another, to
store
> the data. In looking at his code he had a statement like this:

> BEGIN TRANSACTION
> SET XACT_ABORT ON

> However, in looking at the online help, they had the sequence of
statements
> like this:

> SET XACT_ABORT ON
> BEGIN TRANSACTION

> My question is, does it matter?

> Rod


 
 
 

Correct placement of SET XACT_ABORT ON

Post by Rod » Sun, 24 Aug 2003 17:42:59


OK, then, we must be having another problem.  We have 2 stored procedures
which start a transaction, set XACT_ABORT on and then insert values into 2
different tables, but I know for a fact that sometimes one table gets its
values but its child table does not.  I know I am not showing the code, (I
don't have it here with me at the moment), but generally speaking what could
be causing this problem?

Rod


> Apparently, it does not matter (at least in v7).  Try the following as is
> and notice that the batch continues to the end.  Then uncomment one of the
> xact_abort lines, run , etc.  Regardless of placement, the batch aborts at
> the 2nd insert.

> SET ANSI_WARNINGS ON
> go
> IF OBJECT_ID('TEST') is not null DROP TABLE TEST
> GO

> CREATE TABLE TEST ( CTEST CHAR(10))
> GO

> SET XACT_ABORT OFF
> --SET XACT_ABORT ON
> BEGIN TRANSACTION
> --SET XACT_ABORT ON
> INSERT TEST (CTEST) VALUES ( '0123456789')
> INSERT TEST (CTEST) VALUES ( '01234567890')
> SELECT 'CONTINUE'
> ROLLBACK TRAN
> GO

> IF OBJECT_ID('TEST') is not null DROP TABLE TEST
> GO



> > One of my developers has used the T-SQL command SET XACT_ABORT ON (which
I
> > confess I was unfamiliar with) in his stored procedures. I have reason
to
> > believe that the transaction is failing, for one reason or another, to
> store
> > the data. In looking at his code he had a statement like this:

> > BEGIN TRANSACTION
> > SET XACT_ABORT ON

> > However, in looking at the online help, they had the sequence of
> statements
> > like this:

> > SET XACT_ABORT ON
> > BEGIN TRANSACTION

> > My question is, does it matter?

> > Rod

 
 
 

Correct placement of SET XACT_ABORT ON

Post by Uri Diman » Mon, 25 Aug 2003 11:50:45


Rod
BOL says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time
error, the entire transaction is terminated and rolled back. When OFF, only
the Transact-SQL statement that raised the error is rolled back and the
transaction continues processing. Compile errors, such as syntax errors, are
not affected by SET XACT_ABORT.


> OK, then, we must be having another problem.  We have 2 stored procedures
> which start a transaction, set XACT_ABORT on and then insert values into 2
> different tables, but I know for a fact that sometimes one table gets its
> values but its child table does not.  I know I am not showing the code, (I
> don't have it here with me at the moment), but generally speaking what
could
> be causing this problem?

> Rod



> > Apparently, it does not matter (at least in v7).  Try the following as
is
> > and notice that the batch continues to the end.  Then uncomment one of
the
> > xact_abort lines, run , etc.  Regardless of placement, the batch aborts
at
> > the 2nd insert.

> > SET ANSI_WARNINGS ON
> > go
> > IF OBJECT_ID('TEST') is not null DROP TABLE TEST
> > GO

> > CREATE TABLE TEST ( CTEST CHAR(10))
> > GO

> > SET XACT_ABORT OFF
> > --SET XACT_ABORT ON
> > BEGIN TRANSACTION
> > --SET XACT_ABORT ON
> > INSERT TEST (CTEST) VALUES ( '0123456789')
> > INSERT TEST (CTEST) VALUES ( '01234567890')
> > SELECT 'CONTINUE'
> > ROLLBACK TRAN
> > GO

> > IF OBJECT_ID('TEST') is not null DROP TABLE TEST
> > GO



> > > One of my developers has used the T-SQL command SET XACT_ABORT ON
(which
> I
> > > confess I was unfamiliar with) in his stored procedures. I have reason
> to
> > > believe that the transaction is failing, for one reason or another, to
> > store
> > > the data. In looking at his code he had a statement like this:

> > > BEGIN TRANSACTION
> > > SET XACT_ABORT ON

> > > However, in looking at the online help, they had the sequence of
> > statements
> > > like this:

> > > SET XACT_ABORT ON
> > > BEGIN TRANSACTION

> > > My question is, does it matter?

> > > Rod

 
 
 

Correct placement of SET XACT_ABORT ON

Post by Erland Sommarsko » Mon, 25 Aug 2003 21:22:54



> OK, then, we must be having another problem.  We have 2 stored
> procedures which start a transaction, set XACT_ABORT on and then insert
> values into 2 different tables, but I know for a fact that sometimes one
> table gets its values but its child table does not.  I know I am not
> showing the code, (I don't have it here with me at the moment), but
> generally speaking what could be causing this problem?

The problem with SET XACT_ABORT on is that it does not affect all
errors. It does not affect:

o  Compilation errors, for instance missing table, errors in dynamic SQL
   etc.
o  Error 266, trancount mismatch when leaving a stored procedure.
o  RAISERROR.

Note that due to deferred name resolution, compilation errors may well
happen at run-time. If a stored procedure refers to a non-existing table,
the query will fail when you reach that execution path. See further
http://www.veryComputer.com/~sommar/error-handling-I.html#scope-*.

If this does not help you in the right direction, I suggest that you
post the code, or a sample that demonstrates what's happening.

--

Books Online for SQL Server SP3 at
http://www.veryComputer.com/