Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 20, 2005

Find TCP/IP Sockets Net-Libraries port number used by SQL Server 2000

By Muthusamy Anantha Kumar aka The MAK

When a DBA is managing many different SQL Server boxes, it can be a tedious job to find the port numbers used by all of the SQL Servers. In this article, I would like to discuss several methods for finding the Port numbers used by SQL Server.

Method 1

In method one, we are going to take advantage of the MS-DOS batch file, OSQL utility, and the extended stored procedure "xp_regread".

Method 1 can be used only if the SQL Server service in the listed machines is up and running and the SQL Login used in the Findport.bat has permission to access SQL server and has permission to query the registry of that machine.

Step 1

Create Folder C:\Findport, [Refer to Fig 1.0] and create C:\Findport\servers.txt. List all of the server names and the named instance names in the text file, as shown below.


I have published two different articles on how to find all of the SQL Servers on the network. You can use those articles to publish this servers.txt file. Please refer to Scanning the network for SQL Server for a range of IP addresses and Scanning the network for SQL Server.

[Fig 1.0]

Step 2

Create C:\Findport\port.sql as shown below

set nocount on
DECLARE @test varchar(20), @key varchar(100)
if charindex('\',@@servername,0) <>0
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\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)

Download port.sql.

Step 3

REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Date created: Jan 1, 2005
REM Execute osql Servers.txt
for /f "tokens=1,2,3" %%i in (C:\Findport\Servers.txt) 
  do OSQL -S%%i -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql

(Please change -Usa and -Psa to your login and password according to your environment or remove -Usa -Psa and add -E to use windows authentication).

Download Findport.bat_

Step 4

Run the batch file as shown below. [Refer Fig 1.1]

[Fig 1.1]

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

Step 5

Open the c:\ Findport\Findport.log file. The result should look like that shown below.

C:\findport>REM Type: Batch File 
C:\findport>REM Created by: MAK 
C:\findport>REM Contact: mak_999@yahoo.com 
C:\findport>REM Date created: Jan 1, 2005 
C:\findport>REM Execute osql Servers.txt 
C:\findport>for /F "tokens=1,2,3" %i in (C:\Findport\Servers.txt) 
  do OSQL -S%i -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
C:\findport>OSQL -SSQL -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
 Server Name: SQL Port Number:2433
C:\findport>OSQL -SMyComp -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
 Server Name: MYCOMP Port Number:1433
C:\findport>OSQL -SMyComp\Blonde -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
 Server Name: MYCOMP\BLONDE Port Number:2433

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM