/*-------------------------------------------------------------------------------------------------------------------------------Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG
Author : DBATAG
Created on : 09/01/2011
Modified on : 09/01/2011
Version : 1.0
Dependencies :
Table Procedure Permissions
No Dependencies No Dependencies View Server State Permissions Required
----------------------------------------------------------------------------------------------------------------------------*/
-- Connectivity informations
;WITH con AS
(SELECT SES.host_name AS HostName
,CON.client_net_address AS ClientAddress
,SES.login_name AS LoginName
,SES.program_name AS ProgramName
,EP.name AS ConnectionTyp
,CON.net_transport AS NetTransport
,CON.protocol_type AS ProtocolType
,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex
,SES.client_interface_name AS ClientInterface
,CON.encrypt_option AS IsEncryted
,CON.auth_scheme AS Auth
FROM sys.dm_exec_connections AS CON
LEFT JOIN sys.endpoints AS EP
ON CON.endpoint_id = EP.endpoint_id
INNER JOIN sys.dm_exec_sessions as SES
ON CON.session_id = SES.session_id)
-- Detailed list
SELECT *
FROM con
ORDER by con.TDSVersionHex,con.HostName
,con.LoginName
,con.ProgramName;
/*
-- Count of different connectivity parameters
SELECT COUNT(*) AS [Connections #]
,COUNT(DISTINCT con.HostName) AS [Hosts #]
,COUNT(DISTINCT con.LoginName) AS [Logins #]
,COUNT(DISTINCT con.ProgramName) AS [Programs #]
,COUNT(DISTINCT con.NetTransport) AS [NetTransport #]
,COUNT(DISTINCT con.TDSVersionHex) AS [TdsVersions #]
,COUNT(DISTINCT con.ClientInterface) AS [ClientInterfaces #]
FROM con
*/
