Scanning the network for SQL ServerAugust 24, 2004 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-requisitea. 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 1Create a folder named C:\Version. Step 2Create 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 3Create 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 4Go to Command window and execute the batch file as shown in the figure (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.
Content of SQLServersRAW.txt
Servers:
(local)
CLAIRE
EBONY
CLAIRE1
EBONY1
Content of SQLServers.txt(local) CLAIRE EBONY CLAIRE1 EBONY1 Content of log.txtScan 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 ConclusionThis 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
