Other ways to see user options

Published 15 June 09 09:00 AM | MartinBell

After my last post it got me thinking about other ways to find out what a the setting are for a connection. The first one that springs to mind is..

This will give the SET options for the current current connections. This will give you additional information to @@OPTIONS such as language, datefirst and dateformat settings.

Set Option Value
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed

But what if you want to see the settings for an existing connection?

SQL Profiler
Using the ExistingConnection most of information returned from @@OPTIONS or DBCC USEROPTIONS, but there are differences. For instance, if I connected in a SSMS Query window and then SET NOCOUNT ON, SQL Profiler does not pick this up, whereas the other two methods will indicate it is set. The ExistingConnection does give you the network protocol being used, this can be very useful to know.

Exesting Connection

The ExistingConnection is one of the events profiled when you choose to “Trace process in SQL Server Profiler” after right clicking the process in Activity Monitor.

Profile From Activity Monitor

You can also get this information from the dynamic management view sys.dm_exec_sessions. This DMV has bit columns for quoted_identifier, arithabort, ansi_null_dflt_on, ansi_warnings, ansi_padding, ansi_nulls and concat_null_yields_null. If the setting is set it will contain a value of 1.

To get the information for your current session you can use the query:

SELECT * FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

As an exercise I though would try and return this information as a result set similar to my last post, and to do this would require the columns to be transposed into rows i.e. UNPIVOT. I have to admit I have never used UNPIVOT outside demos and answering questions in the newsgroups!! I came up with:

SELECT a.val, a.code, a.description
   (SELECT session_id, quoted_identifier * 256 AS quoted_identifier, arithabort * 64 AS arithabort , ansi_null_dflt_on * 1024 AS ansi_null_dflt_on , ansi_warnings * 8 AS ansi_warnings , ansi_padding * 16 AS ansi_padding , ansi_nulls * 32 AS ansi_nulls , concat_null_yields_null * 4096 AS concat_null_yields_null  
FROM sys.dm_exec_sessions
            WHERE session_id = @@SPID) p
   (code FOR setting IN 
      (quoted_identifier, arithabort, ansi_null_dflt_on, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null)
)AS unpvt
SELECT 1 as val, 'DISABLE_DEF_CNST_CHK' as code,'Controls interim or deferred constraint checking.' as description
UNION ALL SELECT 2,'IMPLICIT_TRANSACTIONS','For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.'
UNION ALL SELECT 4,'CURSOR_CLOSE_ON_COMMIT','Controls behavior of cursors after a commit operation has been performed.'
UNION ALL SELECT 8,'ANSI_WARNINGS','Controls truncation and NULL in aggregate warnings.'
UNION ALL SELECT 16,'ANSI_PADDING','Controls padding of fixed-length variables.'
UNION ALL SELECT 32,'ANSI_NULLS','Controls NULL handling when using equality operators.'
UNION ALL SELECT 64,'ARITHABORT','Terminates a query when an overflow or divide-by-zero error occurs during query execution.'
UNION ALL SELECT 128,'ARITHIGNORE','Returns NULL when an overflow or divide-by-zero error occurs during a query.'
UNION ALL SELECT 256,'QUOTED_IDENTIFIER','Differentiates between single and double quotation marks when evaluating an expression.'
UNION ALL SELECT 512,'NOCOUNT','Turns off the message returned at the end of each statement that states how many rows were affected.'
UNION ALL SELECT 1024,'ANSI_NULL_DFLT_ON','Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit
nullability are defined to allow nulls.'
UNION ALL SELECT 2048,'ANSI_NULL_DFLT_OFF','Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without
explicit nullability do not allow nulls.'
UNION ALL SELECT 4096,'CONCAT_NULL_YIELDS_NULL','Returns NULL when concatenating a NULL value with a string.'
UNION ALL SELECT 8192,'NUMERIC_ROUNDABORT','Generates an error when a loss of precision occurs in an expression.'
UNION ALL SELECT 16384,'XACT_ABORT','Rolls back a transaction if a Transact-SQL statement raises a run-time error.'
      ) a
WHERE  unpvt.code & a.val = a.val 
ORDER BY a.val ;

It is probably easier to string match on the settings, so I modified it to this.
Filed under:


# Handy SQL Server Function (you may not have known about) | Blinking Blog said on November 18, 2010 03:08 AM:

Pingback from  Handy SQL Server Function (you may not have known about) | Blinking Blog

# Robots Blog » Handy SQL Server Function (you may not have known about) said on November 19, 2010 03:02 AM:

Pingback from  Robots Blog » Handy SQL Server Function (you may not have known about)

# Web SEO said on October 17, 2014 01:52 AM:

Other ways to see user options - Martin Bell UK SQL Server MVP

This Blog

SQL Blogs