SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / SQL Server Port Number Identifier / Latest Posts

RE: SQL Server Port Number Identifier

muthukkumaran Kaliyamoorthy — Tue, 24 May 2011 01:32:47 GMT

[quote][b]M A Srinivas (5/24/2011)[/b][hr]When I run on my instance (dynamic port) this gives null . Not much use Following gives the portdeclare @tcpport varchar(1000),@value varchar(250);set @tcpport = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;select @value[/quote]Did you use the script for right version?Have you given correct file paths. Also check the [b]select @value[/b]Try this[code="other"]declare @tcpport varchar(1000),@value varchar(250);set @tcpport = 'SOFTWARE\Microsoft\MSSQLServer\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;select @value[/code]

RE: SQL Server Port Number Identifier

M A Srinivas — Tue, 24 May 2011 01:06:48 GMT

When I run on my instance (dynamic port) this gives null . Not much use Following gives the portdeclare @tcpport varchar(1000),@value varchar(250);set @tcpport = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;select @value

RE: SQL Server Port Number Identifier

Rudy Panigas — Wed, 16 Feb 2011 12:23:10 GMT

[quote][b]anand13685 (2/16/2011)[/b][hr]If you have access to SQL server error logs...which any DBA would....just check the time when the SQL server was last started, and in the startup parameters, it gives the port that the instance is listening on. or open sql server configuration manager, and click on the tcp properties where you can set the port on which SQL should listen.[/quote]Good point. I was doing that before and thought that there must be an easier way. So I worked on some T-SQL code and that's how this code was created.Thanks

RE: SQL Server Port Number Identifier

Rudy Panigas — Wed, 16 Feb 2011 12:21:00 GMT

[quote][b]Thordog (1/21/2011)[/b][hr][quote][b]Rudy Panigas (1/21/2011)[/b][hr]The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server. Give it a try and let us know.Rudy[/quote]Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?[/quote]Glad it worked.

RE: SQL Server Port Number Identifier

anand13685 — Wed, 16 Feb 2011 09:31:41 GMT

If you have access to SQL server error logs...which any DBA would....just check the time when the SQL server was last started, and in the startup parameters, it gives the port that the instance is listening on. or open sql server configuration manager, and click on the tcp properties where you can set the port on which SQL should listen.

RE: SQL Server Port Number Identifier

Thordog — Fri, 21 Jan 2011 14:00:40 GMT

[quote][b]Rudy Panigas (1/21/2011)[/b][hr]The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server. Give it a try and let us know.Rudy[/quote]Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?

RE: SQL Server Port Number Identifier

Rudy Panigas — Fri, 21 Jan 2011 11:24:48 GMT

The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server. Give it a try and let us know.Rudy

RE: SQL Server Port Number Identifier

Derrick Smith — Fri, 21 Jan 2011 11:00:06 GMT

Doesn't seem to work on clustered instances..at least not the 3 I tried. :(Very handy in any case though for all non-clustered servers. Going to get much use out of this.

RE: SQL Server Port Number Identifier

Rudy Panigas — Fri, 21 Jan 2011 10:55:41 GMT

Nice addition :-) My original version had SQL server version but I collect that information with another script so I removed it.Glad to see that other find the code useful.Rudy

RE: SQL Server Port Number Identifier

tskelley — Fri, 21 Jan 2011 09:14:57 GMT

Loved the script, as we come across all types of environments and frequently need the extra info.I added a slight tweak to include SQL Version as part of the output:[code="sql"]-- Show instance, SQL version, and port number for SQL 2000/2005/2008/2008R2 Versionset nocount ongoDECLARE @SqlPort Nvarchar(10)DECLARE @instance_name Nvarchar(30)DECLARE @reg_key Nvarchar(500)DECLARE @value_name Nvarchar(20)DECLARE @ProductVersion Nvarchar(10)-- Scan for SQL 2008R2if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'BEGINselect @ProductVersion = '2008R2'select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)if @instance_name is NULLBEGINset @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'--END--ELSE BEGIN--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@reg_key, @value_name='TcpDynamicPorts',@value=@SqlPort outputselect CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as PortEND-- Scan for SQL 2008if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'BEGINselect @ProductVersion = '2008'select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)if @instance_name is NULLBEGINset @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'--END--ELSE BEGIN--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@reg_key, @value_name='TcpDynamicPorts',@value=@SqlPort outputselect CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as PortEND-- Scan for SQL 2005if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'BEGINselect @ProductVersion = '2005'select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)if @instance_name is NULLBEGINset @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'ENDELSE BEGINset @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@reg_key, @value_name='TcpPort',@value=@SqlPort outputselect CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as PortEND-- Scan for SQL 2000if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'BEGINselect @ProductVersion = '2000'Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'select @SqlPort = Data from #Port_2000select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Portdrop table #Port_2000END[/code]

RE: SQL Server Port Number Identifier

SQLRNNR — Thu, 20 Jan 2011 20:18:26 GMT

Looks good. Thanks for posting the script.

SQL Server Port Number Identifier

Rudy Panigas — Thu, 20 Jan 2011 20:16:17 GMT

Comments posted to this topic are about the item [B]SQL Server Port Number Identifier[/B]