How to find SQL Server Port Number from Management Studio?
Sometimes it is easier to run a query to find SQL Server Port number than having to remote into the server - You can find SQL Server Port number using below T-SQL query.
SET NOCOUNT ON
DECLARE @port varchar(30), @key varchar(150)
IF charindex('\',@@servername,0) <>0
BEGINSET @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGINSET @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'
ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,@value_name='Tcpport',@value=@port OUTPUT
SELECT 'SQL Server Name: '+@@servername + ' Port # '+convert(varchar(20),@port)
I liked this article very much. The content is very good. Keep posting.
ReplyDeleteSQL Server Online Training
Hi,
ReplyDeleteWhen i excute above query i am getting below error could you please provide the solution,
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'BEGINSET'.
SET NOCOUNT ON;
DeleteDECLARE @port VARCHAR(30),
@key VARCHAR(150);
IF CHARINDEX('\', @@servername, 0) <> 0
BEGIN
SET @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\Supersocketnetlib\TCP';
END;
ELSE
BEGIN
SET @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP';
END;
EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = @key,
@value_name = 'Tcpport',
@value = @port OUTPUT;
SELECT 'SQL Server Name: ' + @@servername + ' Port # ' + CONVERT(VARCHAR(20), @port);
--Query to check TCP port Instance is listening to
ReplyDeleteSELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL;