Presents your 
SQL SERVER E-NEWSLETTER for July 25, 2002

<------------------------------------------->

LOCATE THE XACT_ABORT SETTING

The user connection option, XACT_ABORT, does not show up in Query
Analyzer's GUI for user connection options. It also does not appear in 
SQL Server 7.0's global variable @@OPTIONS. You can, however, find the 
setting in SQL Server 2000 through the @@OPTIONS global variable. In SQL Server
7.0, you will need to test to ensure that the user connection option 
has been set either on or off.

The following sample script illustrates the @@OPTIONS value and how
XACT_ABORT affects result sets.

SET XACT_ABORT OFF
GO
SELECT @@OPTIONS
GO
SET XACT_ABORT ON
GO
SELECT @@OPTIONS
GO
-- Different settings for SQL Server 2000
-- Same setting for SQL Server 7.0
SET NOCOUNT ON
GO
CREATE TABLE test1 (key1 INT CONSTRAINT pk_test1 PRIMARY KEY)
CREATE TABLE test2 (fkey1 INT REFERENCES test1(key1))
GO
INSERT INTO test1 VALUES (111)
INSERT INTO test1 VALUES (113)
INSERT INTO test1 VALUES (114)
INSERT INTO test1 VALUES (116)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO test2 VALUES (111)
INSERT INTO test2 VALUES (112) /* Foreign key error */
INSERT INTO test2 VALUES (113)
COMMIT TRAN
GO
SELECT '' 'Only values 111 & 113 should be present', * FROM test2
GO
DELETE test2
GO
SELECT  '' 'Deleted all values' , * FROM test2
GO
SET XACT_ABORT ON
GO
BEGIN TRAN
INSERT INTO test2 VALUES (114)
INSERT INTO test2 VALUES (115) /* Foreign key error */
INSERT INTO test2 VALUES (116)
COMMIT TRAN
GO
SELECT  '' 'Whole transaction should have aborted' , * FROM test2
GO
DROP TABLE test2, test1
GO

----------------------------------------