Tuesday, September 23, 2014

Script to find port number of a SQL Server instance in Multiple ways

--Method 1
DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
 ,@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
 ,@value_name = 'TcpPort'
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port


--Method 2
sp_readerrorlog 1
 ,1
 ,'listening'
 ,'server'





--Method 3
SELECT SERVERNAME = CONVERT(NVARCHAR(128), SERVERPROPERTY('SERVERNAME'))
 ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
 ,local_tcp_port AS 'PortNumber'
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID

No comments:

Post a Comment