SQL Server Port Number Identifier

  • Comments posted to this topic are about the item SQL Server Port Number Identifier

    Rudy

  • Looks good. Thanks for posting the script.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 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:

    -- Show instance, SQL version, and port number for SQL 2000/2005/2008/2008R2 Version

    set nocount on

    go

    DECLARE @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 2008R2

    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'

    BEGIN

    select @ProductVersion = '2008R2'

    select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

    if @instance_name is NULL

    BEGIN

    set @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'

    END

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key=@reg_key, @value_name='TcpDynamicPorts',

    @value=@SqlPort output

    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

    END

    -- Scan for SQL 2008

    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'

    BEGIN

    select @ProductVersion = '2008'

    select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

    if @instance_name is NULL

    BEGIN

    set @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'

    END

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key=@reg_key, @value_name='TcpDynamicPorts',

    @value=@SqlPort output

    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

    END

    -- Scan for SQL 2005

    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'

    BEGIN

    select @ProductVersion = '2005'

    select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

    if @instance_name is NULL

    BEGIN

    set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'

    END

    ELSE BEGIN

    set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

    END

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key=@reg_key, @value_name='TcpPort',

    @value=@SqlPort output

    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

    END

    -- Scan for SQL 2000

    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'

    BEGIN

    select @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_2000

    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

    drop table #Port_2000

    END

  • 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

    Rudy

  • 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.

  • 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

    Rudy

  • Rudy Panigas (1/21/2011)


    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

    Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?

    [font="Courier New"]____________________________________________________________________________________________
    Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
    [/font]

  • 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.

  • Thordog (1/21/2011)


    Rudy Panigas (1/21/2011)


    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

    Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?

    Glad it worked.

    Rudy

  • anand13685 (2/16/2011)


    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.

    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

    Rudy

  • When I run on my instance (dynamic port) this gives null . Not much use

    Following gives the port

    declare @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

  • M A Srinivas (5/24/2011)


    When I run on my instance (dynamic port) this gives null . Not much use

    Following gives the port

    declare @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

    Did you use the script for right version?

    Have you given correct file paths. Also check the select @value

    Try this

    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

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply