Wednesday, April 10, 2013

How to know the Connection Properties in SQL Server 2008


To know the Connection Properties in SQL Server 2008
CONNECTIONPROPERTY ('property')

Is used to get the information about the connection properties
In CONNECTIONPROPERTY() function to get the various property values you need to pass the valid property name.


Example:-
SELECT
CONNECTIONPROPERTY('local_net_address') AS 'IP',
CONNECTIONPROPERTY('local_tcp_port') AS 'PORT',
CONNECTIONPROPERTY('net_transport') AS 'Transport Protocol',
CONNECTIONPROPERTY('protocol_type') AS 'Protocol Type',
CONNECTIONPROPERTY('auth_scheme') AS 'Authentication Scheme',
CONNECTIONPROPERTY('client_net_address') AS 'Client Address'


To check another value the following tables can be used
1
sys.dm_Exec_Sessions
2
sys.dm_Exec_Connections
3
sys.dm_Exec_Requests
4
sys.dm_Broker_Connections


Example:

Query to get the information of current session

SELECT TOP 10 * FROM  sys.dm_Exec_Connections Con
INNER JOIN sys.dm_Exec_Sessions S ON Con.session_id = S.session_id
INNER JOIN  sys.dm_Exec_Requests REQ ON con.connection_id=req.connection_id



Post a Comment