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]