A colleague (Cornel Sukalla) asked me what the protocol_version in sys.dm_exec_connections is all about. From the output it seems that its an INT presenting the version for the protocol used by the client. With some research and having in mind that those cryptic numbers are only another presentation of the “right” value; I came across the following blog post by Xinwei Hong on the SQL Protocols blog. Basically it talks about the matching between server and client TDS protocol implementation and the compatibility between those. You see that the information of the TDS protocol is done through a hex value which is decrypted to the following values.
1: SELECT CONVERT(VARBINARY(9),1930035203)
The client protocol is not specific for the machine which is connecting, it is specific for the client stack which is creating the TDS stream for the communication. You can see this if you are connecting with either DBLibrary, ODBC, OLEDB or ADO.NET. Therefore you can have different values in the management view for the protocol_version per machine, depending on the client stack being used. The following table shows a sample of a machine connecting with different clients:
|Application||Client stack||protocol_version||Hex version|
|Common ADO.NET app (like SQLCMD)||ADO.NET||1913192450||0x72090002|
|Common OLEDB app (like Query Analyzer)||SQLOLEDB||1895825409||0x71000001|
The negotiation for the used TDS version for the communication takes place between the client and the server. The entire session will use this protocol version specifying the supported features, functionality and data types available.
A comprehensive list of the implementation and the possible version can be seen here: