Cool Tips‎ > ‎Connectivity‎ > ‎

Connectivity - 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]



 

ċ
Compare Settings of 2 connections.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
v.3
Comments