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 Aug 24, 2004

Scanning the network for SQL Server

By Muthusamy Anantha Kumar aka The MAK

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



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