When working with SQL Server, it is important to know which version of the SQL Server Native Client (SNAC) is being used by your SQL connections. The SNAC contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one native dynamic link library (DLL) that supports applications using native-code APIs (ODBC, OLE DB, and ADO) to connect to Microsoft SQL Server.
There are different versions of SNAC, with SNAC 9 introduced with SQL Server 2005 and SNAC 10 introduced with SQL Server 2008. It is crucial to verify the version of SNAC being used because SNAC 10 offers additional features that are not supported in SNAC 9.
To determine the version of SNAC used by your SQL connections, you can utilize the sys.dm_exec_connections view in SQL Server. This view contains a column named protocol_version, which indicates the version of the data access protocol associated with each connection.
Here is an example query that retrieves a list of connections and their protocol versions:
SELECT protocol_type, protocol_version, CONVERT(binary(4), protocol_version)
FROM sys.dm_exec_connections;The protocol_version column may display a cryptic hexadecimal value. To interpret this value, you can refer to the following table:
| SQL Server Client to Server | Server to Client | |
|---|---|---|
| 7.0 | 0x00000070 | 0x07000000 |
| 2000 | 0x00000071 | 0x07010000 |
| 2000 SP1 | 0x01000071 | 0x71000001 |
| 2005 | 0x02000972 | 0x72090002 |
| 2008 | 0x03000B73 | 0x730B0003 |
To make the output easier to read, you can modify the query to display the driver version instead of the hexadecimal value. Here is an example:
SELECT session_id, protocol_type,
driver_version = CASE SUBSTRING(CAST(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'
ELSE 'Unknown driver'
END,
client_net_address, client_tcp_port, local_tcp_port, T.text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS T;The output of this query will show you the driver version used by your client applications.
By determining the SQL Server Native Client version used by your SQL connections, you can ensure compatibility with the features and capabilities offered by different versions of SNAC. This knowledge is essential for troubleshooting and optimizing your SQL Server environment.