INF: How to Determine the Current Settings for @@options

ID: Q156498

The information in this article applies to:

Microsoft SQL Server version 6.5

SUMMARY

SQL Server 6.5 introduces the @@options global variable, which records the current state of a number of user options. While you can select @@options to determine the current settings, it only returns an integer, which can be difficult to interpret. This article describes how to create a stored procedure you can run for a more meaningful display of @@options.

MORE INFORMATION

Run the following script as the system administrator (SA) with either ISQL or ISQL/w:


   use master
   go
   if (exists (select * from sysobjects
      where name = 'sp_currentopts'))
      drop procedure sp_currentopts
   go
   if (exists (select * from sysobjects
      where name = 'sysuseropts'))
      drop table sysuseropts
   go
   create table sysuseropts
   (optid      int      NOT NULL,
   options_set    varchar(25) NOT NULL)
   go
   insert into sysuseropts values (0,'NO OPTIONS SET')
   insert into sysuseropts values (1,'DISABLE_DEF_CNST_CHK')
   insert into sysuseropts values (2,'IMPLICIT_TRANSACTIONS')
   insert into sysuseropts values (4,'CURSOR_CLOSE_ON_COMMIT')
   insert into sysuseropts values (8,'ANSI_WARNINGS')
   insert into sysuseropts values (16,'ANSI_PADDING')
   insert into sysuseropts values (32,'ANSI_NULLS')
   insert into sysuseropts values (64,'ARITHABORT')
   insert into sysuseropts values (128,'ARITHIGNORE')
   insert into sysuseropts values (256,'QUOTED_IDENTIFIER')
   insert into sysuseropts values (512,'NOCOUNT')
   insert into sysuseropts values (1024,'ANSI_NULL_DFLT_ON')
   insert into sysuseropts values (2048,'ANSI_NULL_DFLT_OFF')
   go
   grant select on sysuseropts to public
   go
   create procedure sp_currentopts as
   if @@options <> 0
      select options_set
      from master.dbo.sysuseropts
      where (optid & @@options) > 0
   else
      select options_set
      from master.dbo.sysuseropts
      where optid = 0
   go
   grant execute on sp_currentopts to public
   go

If you then run sp_currentopts, you will get a result set listing the current user options set for your connection. For example, suppose a database administrator (DBA) runs the following commands:


   sp_configure 'user options', 1400
   go
   reconfigure
   go

A user who then logged on and ran sp_currentopts would receive the following:


   options_set
   -------------------------
   ANSI_WARNINGS
   ANSI_PADDING
   ANSI_NULLS
   ARITHABORT
   QUOTED_IDENTIFIER
   ANSI_NULL_DFLT_ON

Likewise, if a user logs on to a system where the sp_configure 'user options' setting is set to 0 (zero) and then issues a SET ANSI_WARNINGS ON command, sp_currentopts would return the following:


   options_set
   -------------------------
   ANSI_WARNINGS

Additional query words: 2.65.0201 ODBC

Keywords : kbusage SSrvISQL SSrvStProc
Version : winnt:6.5
Platform : winnt
Issue type : kbhowto kbinfo