How to retrieve all possible server names through C#

Question

Possible Duplicate:
How to list available instances of SQL Servers using SMO in C#?

I'm writing a quick application which is to present the user with a list of server names/instances he or she may choose from to connect to an SQL Server database. Basically I am trying to populate these options into a combobox, which should have options similar to these:

(local)
Joe-PC\SQLEXPRESS
etc.

I have successfully obtained the databases corresponding to a server, and their respective tables, through ADO.NET. However, I cannot find any code samples which allow me to retrieve the above data.

may be this would help http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx ? but this is mainly the instances of a particular SQL Server — Chandra Sekhar Walajapet, Oct 11 '12 at 13:15
We used to do this using SQL Server Management Objects (SMO) API... try searching for examples using that. — Stewart Ritchie, Oct 11 '12 at 13:17

HichemSeeSharp · Accepted Answer · 2013-09-28T17:11:21.300

5

You can use SMO :

- Just add Microsoft.SqlServer.Smo.dll assembly (You can find it in Program Files\Microsoft SQL Server\100\SDK\Assemblies SQL SERVER 2008) to your project resources.
- You can always refer to MSDN.
- Here is the function you need :

SmoApplication.EnumAvailableSqlServers();

edited Sep 28 '13 at 17:11

answered Oct 11 '12 at 13:18

HichemSeeSharp

3,240
2
22
44

Don't forget to put this in in a background worker otherwise application will suck while looking for instances. – HichemSeeSharp Oct 11 '12 at 14:09
Why, is it a constant process? Does it not just execute once when called? – Dot NET Oct 11 '12 at 14:13
It actually takes a little bit of time to enumerate servers. you can show progress bar in indeterminate state while process is working. – HichemSeeSharp Oct 11 '12 at 14:20

score 1 · Answer 2 · answered Oct 11 '12 at 13:16

One way to do this is to use the SQL Browser Service. You can use the SqlDataSourceEnumerator to enumerate all SQL server instances that are have their SQL Browser service running.

The GetDataSources method will return a DataTable of available instances. The MSDN Documentation provides a code example, too.

This is the same mechanism that SQL Server Management Studio will use to populate its server drop down on the connection dialog.

score -1 · Answer 3 · answered Oct 11 '12 at 13:18

-1

The dirty way to do it, is to have a service that tries to connect to all the servers. If the connection fails, you know the server is offline and not to publish that server as a viable option.

answered Oct 11 '12 at 13:18

Matt Westlake

3,499
7
39
80

How to retrieve all possible server names through C#

3 Answers3