Compare the Settings of 2 Connections

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 27 Sep 2010

Description

Occasionally, code on a SQL Server misbehaves for no apparent reason. Two apparently identical pieces of code behave differently when invoked from different places. I've quite regularly heard a developer saying "Well, it worked on my worstation".

Equally as regularly, the problem can be traced to connection settings being different between the workstation, the server and/or the calling application. Before SQL Server 2005, this was a real challenge to track down and prove. SQL 2005 now provides a DMV that gives access to these settings on a per-connection basis.

This code retrieves the connection settings (ARITHABORT, ANSI NULLS etc) for 2 connections for easy comparison.

NOTE: As a result of the prevalence of these errors at one company (over 100 in-house applications, many still coded using VB6) we added guidelines to the coding standards that specified that connections settings were to be explicilty set for each object created in any database as these would then override any settings on the incoming connections. It took a while to have an effect, but eventually reduced the occurence of this type of error.

Code

FIrst, identify the SPIDs of the sessions you want to compare:

-- first, get a list of all active SPIDs on the system

SELECT Session_ID AS SPID, Login_Name

FROM sys.dm_exec_sessions

ORDER BY Login_Name, Session_ID

-- WHERE Session_ID > 50 --** Include this line to screen out system SPIDs **

Then compare them:

DECLARE

@SPID1 INT,

@SPID2 INT

SELECT

@SPID1 = 52,

@SPID2 = 53

SELECT

CASE

WHEN Session_ID = @SPID1 THEN 'SPID1'

ELSE 'SPID2'

END AS Label,

SDES.[session_id],

SDES.[login_name],

SDES.[text_size],

SDES.[language],

SDES.[date_format],

SDES.[date_first],

CASE SDES.[date_first]

WHEN 1 THEN 'Monday'

WHEN 2 THEN 'Tuesday'

WHEN 3 THEN 'Wednesday'

WHEN 4 THEN 'Thursday'

WHEN 5 THEN 'Friday'

WHEN 6 THEN 'Saturday'

WHEN 7 THEN 'Sunday (default)'

END AS [date_first_desc],

SDES.[quoted_identifier],

SDES.[arithabort],

SDES.[ansi_null_dflt_on],

SDES.[ansi_defaults],

SDES.[ansi_warnings],

SDES.[ansi_padding],

SDES.[ansi_nulls],

SDES.[concat_null_yields_null],

SDES.[transaction_isolation_level],

CASE SDES.[transaction_isolation_level]

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'READUNCOMMITTED'

WHEN 2 THEN 'READCOMMITTED'

WHEN 3 THEN 'REPEATABLE'

WHEN 4 THEN 'SERIALIZABLE'

WHEN 5 THEN 'SNAPSHOT'

END AS [transaction_isolation_level_desc],

SDES.[lock_timeout], SDES.[deadlock_priority]

FROM sys.[dm_exec_sessions] SDES

WHERE SDES.[session_id] IN (@SPID1, @SPID2)

ORDER BY SDES.[session_id]