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

Article translations Article translations
Article ID: 156498 - View products that this article applies to.
This article was previously published under Q156498
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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
				

Properties

Article ID: 156498 - Last Review: October 26, 2013 - Revision: 4.0
Applies to
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbnosurvey kbarchive kbhowto kbinfo kbusage KB156498

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com