Interesting requirement! How to query port numbers from 120 servers!

[DBFan]

I need to document the port numbers from nearly 120 sql servers in the network. Is there any query that I can use to get this info??

Thanks,

[rmiao]

You can get that in registry.

[skhanal]

You can run this in each server (non clustered)

DECLARE @test varchar(20), @key varchar(100)

set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supers ocketnetlib\TCP'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,
@value_name='Tcpport',
@value=@test OUTPUT
SELECT @test

If it is clustered run this

DECLARE @test varchar(20), @key varchar(100)

set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketne tlib\TCP'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,
@value_name='Tcpport',
@value=@test OUTPUT
SELECT @test


You can make it more intelligent by querying fn_virtualservernodes() to list nodes in a cluster, if it returns null then it is a stand alone server.

[MAK]

1. Create and list all the servernames on C:\servers.txt

Server1
Server2
Server3
Server4
Server5

2. Create C:\port.sql as below
set nocount on
DECLARE @test varchar(20), @key varchar(100)
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supers ocketnetlib\TCP'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,
@value_name='Tcpport',
@value=@test OUTPUT
SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@test)


3. Create c:\findport.bat
REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Execute osql Servers.txt

for /f "tokens=1,2,3" %%i in (C:\Servers.txt) do OSQL -S%%i -t0 -E -dMASTER -ic:\PORT.sql

4. Run the batch file

c:\findport.bat > C:\Findport.log

5. Open the c:\Findport.log file.

[DBFan]

Great! Before I try these options, is there a way to run a single script which will find the sql servers by itself across the network and generate the port numbers, as we have no list of servers available ready..:-)

[DBFan]

Skhanal,

When I ran your scripts both of them are giving 'NULL' as output, any idea?

[rmiao]

You can get server list with following command in dos prompt:

osql -L

[DBFan]

Hi MAk,

When I ran the bat file I am getting the following error:

bat file:

for /f "tokens=1,2,3" %%i in (C:\Servers.txt) do OSQL -S%%i -t0 -E -dMASTER -ic:\PORT.sql

error:
output
--------------------------------
%%i was unexpected at this time.
NULL

Any idea MAK?

[MAK]

a. I wrote 2 articles on how to find sql server on the network.

http://www.databasejournal.com/article.php/3300441

2. when you copied and pasted skhanal's script it added a carriage return between

Supers and ocketnetlib\TCP'

Bring every broken line to a single line.

3. Follow No.2 in my batch file and sql statements.

[DBFan]

Hi MAK,
I did correct the script to bring it
into one line when I got this error. Any thing else missing here?

[DBFan]

Hi Skhanal? Any help please?

[MAK]

please find attached file

[DBFan]

Thanks MAK, but I am getting the same error again..any idea?

[skhanal]

Check the registry setting in one of your servers to see if the tcpport key exists and if the location is correct in the script.