list of the SQL Servers on a network

[password99]

Is there a way to get a list of the SQL Servers on a network through some mechanism supplied with the SQL Server (interface)?

 

[password99]

Is there a way to call ListAvailableSQLServers() from ODBC thru Transact-SQL?

 

[mrdenny]

I was able to come up with a way to do it, but not through the interface. It's done through a couple of batch files and some "for" statements.

First get a listing of all machines on your network with "net view > netview.txt". Then go through that file calling checkbox.bat for each line in the text file. You will need to control c the first two lines.

for /f "tokens=1" %%a in (netview.txt) do checkbox.bat %%a

In the checkbox.bat use sc to check the machine name that was passed and log the output to a text file. You will need a copy of grep.exe for this step.

sc %1 query bufsize= 10000 state= all | grep -i "SERVICE_NAME: MSSQL" > servlist.tmp

Then use another "for" statement to go through the servlist.tmp file looking for the service_name: line. This will tell you that the machine has SQL installed. Log that output to a final text file.

Here are the full batch files.

REM run.bat starts here.
@ECHO OFF
del namelist.txt
del netview.txt
net view > netview.txt
@for /f "tokens=1" %%a in (netview.txt) do checkbox.bat %%a
echo .
echo Done
REM run.bat ends here.

REM CheckBox.bat starts here.
@ECHO OFF
if "%1" == "" goto ErrorInParm
echo Checking %1

del *.tmp
copy blank.txt tempname.tmp > 1.tmp

sc %1 query bufsize= 10000 state= all | grep -i "SERVICE_NAME: MSSQL" > servlist.tmp
@for /f "tokens=1" %%b in (servlist.tmp) do if "%%b" == "SERVICE_NAME:" echo %1 > tempname.tmp

type tempname.tmp >> namelist.txt

goto ExitBatch

:ErrorInParm
echo ERROR.  No input paramater.
goto ExitBatch

:ExitBatch
REM CheckBox.Bat ends here.

This works pretty well. Run times will depend on the number of machines on your network.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[password99]

Thanks for sharing your code.

I found something more

http://sqldev.net/misc/ListSQLSvr.htm