Post reply

Script to list ports

  • October 22, 2007 at 6:27 am

    #62476

    Hi everyone.

    Could anyone help me out a script to list ports that SQL is using ?

    Need to identify ports being used by SQL 2000 MSDE environment.

    many thanks

  • October 22, 2007 at 6:46 am

    #744110

    here you g; this works for both 2000 and 2005, you have to run this in each instance:

    typical results:

    ServerName port

    DAISY\SQLEXPRESS3255

    CREATE TABLE #GetPort

    (

    token varchar(100),

    value varchar(20))

    go

    DECLARE @inst varchar(200)

    DECLARE @inst1 varchar(100)

    --Determine registry path and key

    IF(charindex('\',@@servername) > 0)

    BEGIN

    SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

    SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

    --SELECT @inst1 = 'TcpDynamicPorts'

    SELECT @inst1 = 'TcpPort'

    END

    ELSE

    BEGIN

    if SUBSTRING(@@VERSION,23,1) = '7'

    begin

    SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

    SELECT @inst1 = 'DefaultServerPort'

    end

    else

    begin

    SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

    SELECT @inst1 = 'DefaultPort'

    end

    END

    print @inst + '\\\\////'+ @inst1

    INSERT #GetPort

    EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

    SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

    DROP TABLE #GetPort

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 22, 2007 at 6:53 am

    #744114

    Lowell

    spot on many thanks 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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