Presents your 
SQL SERVER E-NEWSLETTER for June 6, 2002

VARY BEHAVIORS BY SETTING USER OPTIONS

The @@OPTIONS is an integer value that holds ANSI settings for a user's
session. The DBCC USEROPTIONS command reports ANSI settings for a user in
a readable format as well.

It's a good idea to find out what options you are issuing T-SQL commands
under when creating or modifying database objects. Additionally, it's
good practice to be consistent with ANSI settings when creating database
objects and issuing DML commands against tables. Doing so will make it easier 
to debug when table results are different or when a stored procedure is 
functioning in an unexpected manner.

The following script displays the user options that are set for the
current session. Use this script to assist in determining what ANSI settings
and user options are set within connection settings.
The script can help you gain a better understanding of the varying
behaviors of setting different user options.

DBCC USEROPTIONS
GO
SELECT @@TEXTSIZE text_size, @@DATEFIRST datefirst, @@LANGUAGE language
GO
IF (@@OPTIONS & 1 ) 0
BEGIN
     RAISERROR( 'DISABLE_DEF_CNST_CHK',10,1)
END
IF (@@OPTIONS & 2 ) 0
BEGIN
     RAISERROR( 'IMPLICIT_TRANSACTIONS',10,1)
END
IF (@@OPTIONS & 4 ) 0
BEGIN
     RAISERROR( 'CURSOR_CLOSE_ON_COMMIT',10,1)
END
IF (@@OPTIONS & 8 ) 0
BEGIN
     RAISERROR( 'ANSI_WARNINGS',10,1)
END
IF (@@OPTIONS & 16 ) 0
BEGIN
     RAISERROR( 'ANSI_PADDINGS',10,1)
END
IF (@@OPTIONS & 32 ) 0
BEGIN
     RAISERROR( 'ANSI_NULLS',10,1)
END
IF (@@OPTIONS & 64 ) 0
BEGIN
     RAISERROR( 'ARITHABORT',10,1)
END
IF (@@OPTIONS & 128 ) 0
BEGIN
     RAISERROR( 'ARITHIGNORE',10,1)
END
IF (@@OPTIONS & 256 ) 0
BEGIN
     RAISERROR( 'QUOTED_IDENTIFIER',10,1)
END
IF (@@OPTIONS & 512 ) 0
BEGIN
     RAISERROR( 'NOCOUNT',10,1)
END
IF (@@OPTIONS & 1024 ) 0
BEGIN
     RAISERROR( 'ANSI_NULL_DFLT_ON',10,1)
END
IF (@@OPTIONS & 2048 ) 0
BEGIN
     RAISERROR( 'ANSI_NULL_DFLT_OFF',10,1)
END
IF (@@OPTIONS & 4096 ) 0
BEGIN
     RAISERROR( 'CONCAT_NULL_YIELDS_NULL',10,1)
END
IF (@@OPTIONS & 8192 ) 0
BEGIN
     RAISERROR( 'NUMERIC_ROUNDABORT',10,1)
END
IF (@@OPTIONS & 16384 ) 0
BEGIN
     RAISERROR( 'XACT_ABORT',10,1)
END
GO

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