Are you experiencing unexplained SQL Server database issues? Is your web server having issues with symptoms ranging from request time-outs to login failures to deadlocks to memory allocation issues to hangs or unexplained restarts? Many of these issues can be tracked back to mismatched or outdated ODBC drivers.
To avoid these and other potential database issues, it is important that the ODBC drivers in use match the database version being referenced. The driver names are distinct so you can tell them apart at a glance.
|SQL Version||Driver Name|
|SQL Server 2000||SQL Server|
|SQL Server 2005||SQL Native Client|
|SQL Server 2008||SQL Server Native Client 10.0|
|SQL Server 2012||SQL Server Native Client 11.0|
Note that there are multiple builds of each of the drivers and locking issues can also occur with older driver builds (e.g., running SQL Server 2005 with the original ODBC drivers dated October 2005, when an updated driver dated December 2008 is available from Microsoft).
The following query can be used to identify the version (but not build) of ODBC driver used for active connections against SQL Server 2005 or higher databases:
, CAST(A.protocol_version AS VARBINARY(9))
CASE SUBSTRING(CAST(A.protocol_version AS BINARY(4)), 1,1)
WHEN 0x70 THEN ‘SQL Server 7.0’
WHEN 0x71 THEN ‘SQL Server 2000’
WHEN 0x72 THEN ‘SQL Server 2005’
WHEN 0x73 THEN ‘SQL Server 2008’
WHEN 0x74 THEN ‘SQL Server 2012’
ELSE ‘Unknown driver’
FROM sys.dm_exec_connections A
INNER JOIN sys.dm_exec_sessions B ON A.session_id = B.session_id
WHERE B.client_interface_name = ‘ODBC’
AND SUBSTRING(CAST(A.protocol_version AS BINARY(4)), 1,1) <> 0x73
In the example above, the query will return workstation information for all active connections to all databases in the SQL Server instance that are using a driver version other than SQL Server 2008 (0x73). You can replace that last portion with your SQL Server version’s code to identify mismatched drivers being used in your solution. (i.e. 0x70 = SQL Server 7, 0x74 = SQL Server 2012, etc.) Please note that this is only the main version, and won’t help with identifying SQL Server 2005 driver builds of an earlier SP than the server.