Saturday, August 21, 2010

How to find the port no of a particular sql server instance !

Script-1
-------------
For default instance use below code :

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

For a named instance use the below code :
------------------------------------
DECLARE @tcp_port nvarchar(5)

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

select @tcp_port