How to find available SQL Server on local network

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using VB.NET how can I find all available SQL Server on a local network and
list all the databases in each server.

Thanks
 
You can do this using SQL-DMO. Add a reference to sqldmo.dll and sqldmo.rll
to use SQL-DMO. The Application object of SQL-DMO has a ListAvailableServers
method which will give you all SQL Servers on the network (if you are running
Windows 2000 and up).

You can also connect to an SQL Server instance and enumerate all objects for
that server (using the SqlServer object).

Links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;287737
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con03_8q44.asp

Of course, the code will be a bit different in VB.net.

HTH, Jakob.
 
I am getting the following error when i run it on WIN2K

QueryInterface for interface SQLDMO.NameList failed???
 
It seems that this may be a pre-SP2 problem. Did you install all SQL Server
service packs?

Regards, Jakob.
 
Installing SP3a fixed it. Thanks

Have another question
I have installed sql server on stand alone machine, running XP. The code
doesn't generate error but doesn't even shows the instance of sql server
installed on it, what could be the problem? Does the method
ListAvailableSQLServers() only scans the network for instances of sql server
and not the local machine?
 
It seems that the NameList returned by ListAvailableServers is 1-based,
although it does not fail if you use indices smaller than 1 (it just returns
an empty string). So if you use code similar to the following, you will get
a server name '(local)' for the local (default) SQL Server instance:

SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList names = app.ListAvailableSQLServers();
for (int i = 1; i <= names.Count; ++i)
Console.WriteLine(names.Item(i));

HTH, Jakob.
 
Back
Top