Microsoft KB Archive/156498

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

Article ID: 156498

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q156498



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: kbhowto kbinfo kbusage KB156498

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.