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

This article was previously published under Q156498
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
SQL Server 6.5 introduces the @@options global variable, which records thecurrent state of a number of user options. While you can select @@optionsto determine the current settings, it only returns an integer, which can bedifficult to interpret. This article describes how to create a storedprocedure you can run for a more meaningful display of @@options.
More information
Run the following script as the system administrator (SA) with either ISQLor 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 adatabase 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 thefollowing:
   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				
2.65.0201 ODBC
Properties

Article ID: 156498 - Last Review: 10/26/2013 01:19:00 - Revision: 4.0

  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbhowto kbinfo kbusage KB156498
Feedback