SQL Server Port Number Script

  • GregoryF

    SSCertifiable

    Points: 6296

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

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • rVadim

    Hall of Fame

    Points: 3969

    Good script - Thank you.

    But it only runs for the server Iā€™m currently connected to. Did I misunderstand your sentence about iterating thru servers?

    --Vadim.

    --Vadim R.

  • GregoryF

    SSCertifiable

    Points: 6296

    It will iterate through server's if you are using a multi-server query that is available in SSMS2008, otherwise it will show just the server that you are querying against.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • SQLRNNR

    SSC Guru

    Points: 281243

    That is a nice script indeed. Thanks.

    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

  • GregoryF

    SSCertifiable

    Points: 6296

    The multi-server query is a great addition to SSMS, I remember just a couple of years ago when you would have had to RDP into 70 servers to get this type of information (or run a script like this on 70 servers). Now with the multi server query you can look for Builtin\Administrators on all production servers, change the sa password in one swoop, determine if tempDB is on a seperate drive, etc...

    Now if Microsoft will just give us something like Oracle Data Guard and or Oracle RAC...

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • vlad-548036

    Hall of Fame

    Points: 3491

    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.' :doze:

    this is on named instance of sql 2008 on 64-bit 2008 server

  • wmt

    Mr or Mrs. 500

    Points: 572

    vlad-548036 (5/10/2010)


    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.' :doze:

    this is on named instance of sql 2008 on 64-bit 2008 server

    This script only works on the default instance, as the registry entry for named instances are stored elsewhere.

  • vlad-548036

    Hall of Fame

    Points: 3491

    Exactly, I was just wondering whether you know where ?

  • wmt

    Mr or Mrs. 500

    Points: 572

    vlad-548036 (5/26/2010)


    Exactly, I was just wondering whether you know where ?

    I meant to post this earlier. This works for me across my servers\instances.

    It's just a slightly amended version of the original.

    declare @Server as varchar(128)

    declare @KeyToInterogate as varchar(200)

    declare @Version as varchar (512)

    declare @PortNumber as varchar(8)

    set @Server = @@ServerName

    set @Version = left(@@Version, 38)

    set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

    if charindex('\',@@ServerName) > 0

    begin

    set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'

    set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName))

    set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'

    end

    exec xp_regread

    @rootkey = 'HKEY_LOCAL_MACHINE',

    @key = @KeyToInterogate,

    @value_name = 'TcpPort',

    @value = @PortNumber output

    If @PortNumber <> '1433'

    begin

    print '*****server: ' + @server + ' is running on port ' + cast(@PortNumber as varchar) + '. Server is: ' + @Version

    end

    else print 'server: ' + @server + ' is running on port ' + cast(@PortNumber as varchar) + '. Server is: ' + @Version

  • vlad-548036

    Hall of Fame

    Points: 3491

    Excellent! šŸ™‚ Great job!

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the updated 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

  • vvkp

    SSC Journeyman

    Points: 94

    Good work...quick question...!

    If I want to change the port, how can I do that by using TSQL? A kind of reverse process to the work you did.

    Appreciate your time.

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

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