Find SQL Server TCP/IP Port Number

  • Vijendran

    SSC Veteran

    Points: 278

    Comments posted to this topic are about the item Find SQL Server TCP/IP Port Number

  • SQLGerman

    Valued Member

    Points: 65

    I think we should start using the SQL CLR to accomplish all this going forward. But, a good code though.

  • BlackHawk-17

    Mr or Mrs. 500

    Points: 518

    SQLGerman (7/20/2009)


    I think we should start using the SQL CLR to accomplish all this going forward.

    Not every one is capable of coding CLR modules and that's a lot of unnecessary overhead for simple info gathering, IMHO.

  • Rudy Panigas

    SSChampion

    Points: 10695

    Nice code. Any idea where to fine the port number for SQL 2008?

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello everyone,

    Here is the code that will also scan SQL 2008 servers

    --SQL 2000/2005/2008 Version

    set nocount on

    go

    DECLARE @SqlPort Nvarchar(10)

    DECLARE @instance_name Nvarchar(30)

    DECLARE @reg_key Nvarchar(500)

    Declare @value_name Nvarchar(20)

    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),2) = '10'

    BEGIN

    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, @SqlPort as Port

    END

    --------------------------------------------------------------------

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

    BEGIN

    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, @SqlPort as Port

    END

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

    BEGIN

    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, @SqlPort as Port

    drop table #Port_2000

    END

    Rudy

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

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