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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 20, 2005

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

By Muthusamy Anantha Kumar aka The MAK



Method 2

In method two, we are going to take advantage of VB-Scripting and WMI methods.

Method2 requires access to read the registry on all of the listed servers. SQL Server



Step 1

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



SQL 
MyComp
MyComp\Blonde

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.



Step 2

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

'Objective: To find port used by sql server
'Created by: MAK
'Create Date: Jan 9, 2004

on error resume next
Const HKEY_LOCAL_MACHINE 	= &H80000002
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")

InputFile="c:\Findport\servers.txt"
OutputFile="c:\Findport\FindPort.log"

Set ifile = iFSO.OpenTextFile(inputfile)  
Set ofile = ofso.createTextFile(OutputFile, True)

ofile.writeline Now()
ofile.writeline "Find Port process started"

Do until ifile.AtEndOfLine
sComputer	= ifile.readline
sComputer=trim(sComputer)
sComputer2=scomputer
if InStr(1,sComputer,"\",1) <>0 then
len1=instr(1,sComputer,"\",1)-1
sComputer=left(sComputer,len1)
len2=len(sComputer)-instr(1,sComputer,"\",1)
instance=right(sComputer2,len2)
sKey		= "SOFTWARE\MICROSOFT\Microsoft SQL  
Server\"+instance+"\MSSQLServer\Supersocketnetlib\Tcp"
else
sKey		= "SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\Tcp"
end if
'msgbox "COmputer" & sComputer
'msgbox "instance" & instance
sMethod		= "GetStringValue"
hTree		= HKEY_LOCAL_MACHINE
sValue		= "TcpPort"
'mystring 	= 
'msgbox "winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv"
Set oRegistry	=  
GetObject("winmgmts:{impersonationLevel=impersonate}//"&sComputer&"/root/default:StdRegProv")
Set oMethod	= oRegistry.Methods_(sMethod)
Set oInParam	= oMethod.inParameters.SpawnInstance_()
oInParam.hDefKey = hTree
oInParam.sSubKeyName = sKey
oInParam.sValueName = sValue
Set oOutParam = oRegistry.ExecMethod_(sMethod, oInParam)
'WScript.Echo sComputer
'WScript.Echo oOutParam.Properties_("sValue")
ofile.writeline "ServerInstance: "& SComputer2 & " Port: "& Cstr(oOutParam.Properties_("sValue"))
loop


ofile.writeline "Find Port process Completed"
ofile.writeline Now()

Download Findport.vbs_

Step 3

Execute the Findport.vbs as shown below. Refer to fig 1.2.

C:\Findport\Findport.vbs


Fig 1.2

Step 4

The Findport.vbs will complete by showing the following message. Refer to Fig 1.3


[Fig 1.3]

Step 5

Open the c:\ Findport\Findport.log file. The result is displayed below.

1/9/2005 7:50:36 PM
Find Port process started
ServerInstance: sql Port: 2433
ServerInstance: MyComp Port: 1433
ServerInstance: MyComp\Blonde Port: 2433
Find Port process Completed
1/9/2005 7:50:59 PM

Conclusion

As mentioned, the objective of this article is to find the port number used by SQL server in every machine listed.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date