Scanning the network for SQL Server

In a large IT department, it can
be difficult to locate where new SQL Server installations were made and how
many of those SQL Servers were patched, etc. This article explains how to scan
the entire network to find which boxes have SQL Server instances running and how
to identify what version they are.

Pre-requisite

a.  SQL
Server Client installed on the machine where this article is going to be
applied

b. The
login used to the run this batch file should have access to all the SQL Server
machines on the network.

Step 1

Create a folder named C:\Version.

Step 2

Create the batch file, C:\checkversions.bat, and copy and
paste the below given code into it.


REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Date: Aug 18, 2004
REM Find and Scan SQL Server on the network

REM Use SQL to find all the SQL Server
machines on the network
ECHo Scan Started >> c:\version\Log.txt
ECHo Date/t >> c:\version\Log.txt
Echo Time/t >> c:\version\Log.txt
“C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe” -L >
c:\version\sqlserversRAW.txt

REM Cleanup the sqlserversRAW.txt
to a right format
“C:\version\check.vbs”

REM Execute OSQL command on every server listed in sqlserversRAW.txt
to find the version
REM and to write the log in log.txt

for /f “tokens=1,2,3” %%i in (C:\version\sqlservers.txt)
do “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe” -E -S”%%i”
-Q”set nocount on begin select getdate()
end begin select @@servername
end begin Select @@version end” >> c:\version\log.txt

ECHO Scan Completed >> c:\version\Log.txt

Download Code from here.

Step 3

Create the VBS file, C:\check.vbs, and copy and paste the
below given code into it.


‘Objective: Find SQL Server version on the network
‘Author: MAK
‘Contact: mak_999@yahoo.com
‘Date: Aug 18, 2004
Set iFSO = CreateObject(“Scripting.FilesyStemObject”)
Set oFSO = CreateObject(“Scripting.FilesyStemObject”)
InputFile=”c:\version\sqlserversRAW.txt”
Outputfile=”c:\version\SQLServers.txt”

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

‘Read Empty line
Computer = ifile.ReadLine

‘Read Header
Computer = ifile.ReadLine

Do until ifile.AtEndOfLine
Computer = ifile.ReadLine
ofile.writeline ltrim(rtrim(Computer))
Loop

Download Code from here.

Step 4

Go to Command window and execute the batch file as shown in
the figure (Fig 1.1).



(Fig 1.1)

When this batch file is executed, it uses OSQL.exe to find
all of the SQL server machines that are on the network and creates a server list
in c:\version\sqlserversRAW.txt. Then it calls check.vbs, which in turn cleans
up the c:\version\sqlserversRAW.txt to a correct format and creates a clean
c:\version\sqlservers.txt. Then the FOR loop in the batch file executes the OSQL
command for every server listed in c:\version\sqlservers.txt, saving the
results to c:\version\Log.txt.

Figure (Fig 1.2) shows the list of files created when the
batch file is executed.



(Fig 1.2)

Content of SQLServersRAW.txt


Servers:
(local)
CLAIRE
EBONY
CLAIRE1
EBONY1

Content of SQLServers.txt


(local)
CLAIRE
EBONY
CLAIRE1
EBONY1

Content of log.txt


Scan Started
Date/t
Time/t

———————–
2004-08-20 00:33:50.750
——————————————————————————
————————————————–
SQL
——————————————————————————
———————————————————————–
———————————————————————–
———————————————————————–
———
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Cop
yright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windo
ws NT 5.0 (Build 2195: Service Pack 4)
———————–
2004-08-20 00:34:39.763
——————————————————————————
————————————————–
CLAIRE
——————————————————————————
———————————————————————–
———————————————————————–
———————————————————————–
———
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Cop
yright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windo
ws NT 5.2 (Build 3790: )
———————–
2004-08-20 00:27:46.060
——————————————————————————
————————————————–
EBONY
——————————————————————————
———————————————————————–
———————————————————————–
———————————————————————–
———
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Cop
yright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windo
ws NT 5.2 (Build 3790: )

———————–
2004-08-20 00:28:12.763
——————————————————————————
————————————————–
CLAIRE1
——————————————————————————
———————————————————————–
———————————————————————–
———————————————————————–
———
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Cop
yright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windo
ws NT 5.2 (Build 3790: )
———————–
2004-08-20 00:28:26.060
——————————————————————————
————————————————–
EBONY1
——————————————————————————
———————————————————————–
———————————————————————–
———————————————————————–
———
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Cop
yright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windo
ws NT 5.2 (Build 3790: )

Scan Completed

Conclusion

This article has covered a method of scanning the entire
network for SQL Server and its versions. The batch file can be tweaked to
execute many SQL statements on every SQL Server machine on the network.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles