Credits:
Description
Code
|
|
Cool Tips > Connectivity >
Connectivity - Compare the Settings of 2 ConnectionsCredits:Author: Unknown
Date: 27 Sep 2010
DescriptionOccasionally, 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:
Then compare them:
|