Compare the Settings of 2 Connections
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 27 Sep 2010
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.
FIrst, identify the SPIDs of the sessions you want to compare:
-- first, get a list of all active SPIDs on the system
Then compare them: