List SQL Server Instances

Question

Good afternoon,

I have a windows form that I am working with. When I first wrote this code it worked and was able to pull a list of SQL Servers for a ComboBox, and then as I changed servers it would populate another ComboBox with databases. I had not updated the code in a few years, and it is using .net 4.6.1 and the Microsoft.SqlServer.SqlManagementObjects version: 150.18147.0. I went to use the program and launched it now it is not returning any sql servers. I opened up visual studio and went to debug it and it is still not returning servers.
All this code is contained within the form class itself.

private async void ActiveStatuses_LoadAsync(object sender, EventArgs e)  
        {  
            //List all the servers, if only want to see local set to true  
            DataTable dataTable = await System.Threading.Tasks.Task.Run(() => { return SmoApplication.EnumAvailableSqlServers(false); });  
            cbServers.ValueMember = "Name";  
            cbServers.DataSource = dataTable;  
        }  
private void cbServers_SelectedIndexChanged(object sender, EventArgs e)  
        {  
            cbDatabases.Items.Clear();  
            if (cbServers.SelectedIndex != -1)  
            {  
                string serverName = cbServers.SelectedValue.ToString();  
                Server server = new Server(serverName);  
                try  
                {  
                    foreach (Database database in server.Databases)  
                    {  
                        cbDatabases.Items.Add(database.Name);  
                    }  
                }  
                catch (Exception ex)  
                {  
  
                    string excepection = ex.Message;  
                }  
            }  
        }  
  
        private void cbDatabases_SelectedIndexChanged(object sender, EventArgs e)  
        {  
            string databaseName = cbDatabases.SelectedItem.ToString();  
        }  

I cannot see why this would stop working and debugging shows enumeration yielded no results. Also it does output any errors.

Answer 1

Most likely your network no longer has the SQLBrowser service setup. Security usually shuts this down.

Answer 2

Hi

Can you try the below code?

//// Retrieve the enumerator instance, and then retrieve the data sources.  
        SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;  
        DataTable dtDatabaseSources = instance.GetDataSources();  

https://stackoverflow.com/questions/10781334/how-to-get-list-of-available-sql-servers-using-c-sharp-code

Answer 3

I had the same problem on a work machine but not my home computer.

Steps to fix

1. Administrative Tools
2. Services
3. Properties
4. General tab
5. Change Startup to auto
6. Start the service

I work on an area which is ungodly secure (IRS restrictions) so even with the above out of around 20 network servers all I can see is my local machine

Answer 4

See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enumerating-instances-of-sql-server
=> Enumeration Limitations
The method wasn't ever reliable and can return incomplete results.