TSQL to tell which network protocol is being used for each active session

Question

Once upon a time, I remember running some TSQL which would display which network protocol (e.g. named pipes, TCP/IP) was being used by each active session - but I can't remember the actual code. Does anyone know how to do this ? An input parameter might have been SPID. The code was useful for diagnostic purposes.

Answer 1

For SQL Server 2005+ sys.dm_exec_connections has "net_transport" column

sysprocesses has a net_library column valid for older SQL Server too

So, a SELECT net_library FROM sys.sysprocesses WHERE SPID = 123 will give you this info