[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

How to identify the SQL instances installed on a network, using C#?

I need to search for all the SQL Server / SQL Server Express instances installed in a newtwork and display them in a combo box in a C# Application Program.
I tried the following two codes:

But both the methods doesn't give me the results I'm hoping for.
My primary objective is to distinguish between SQL Server 2005 and SQL Server Express, because for another program to run I need my user to be able to select one among those two. But when I run this code it give the following errors:

1) I have above both installed on my computer, but it gives only the server name of SQL Server 2005.

2)On the network, this code completely ignores some of the machines, and it DOES give me both the SQL Server 2005 and Express instances on some other machines on my network. If I run the code from another machine, it still doesn't show that I ahve installed both on my computer.

Experts, please help on this.
//Using smo. 
//Here, localOnly is a bool variable. If true, this method search only the instances on this machine and if set to false search instances on all machines on the network
foreach (DataRow serverRow in SmoApplication.EnumAvailableSqlServers(localOnly).Rows)
{
                cmbServerList.Items.Add(serverRow.ItemArray[0]);
}
 
 
//Here ItemArray[0] stores each SQL server name and ItemArray[1] stores each instance
SqlDataSourceEnumerator sqlInstance = SqlDataSourceEnumerator.Instance;
foreach (DataRow dataSourceRow in sqlInstance.GetDataSources().Rows)
{
                cmbServerList.Items.Add(dataSourceRow.ItemArray[0] + "/" + dataSourceRow.ItemArray[1]);
}

Open in new window

0
sachintha81
Asked:
sachintha81
  • 3
2 Solutions
 
DhaestCommented:
SM0 2005 - Populating a list of SQL Servers
http://www.sqldbatips.com/showarticle.asp?ID=45
0
 
Sreedhar VengalaCommented:

May this helps you: 
using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DataTable Sql_datatable = SmoApplication.EnumAvailableSqlServers(false))
            {
                if (Sql_datatable.Rows.Count > 0)
                {
                    foreach (DataRow dr in Sql_datatable.Rows)
                    {
                        Console.WriteLine(dr["Name"]);
                    }
                }
            }
        }
    }
}

Open in new window

0
 
sachintha81Author Commented:
Dhaest, the last program of your link which combines all the methods pretty much gave me the functionality I desire except for one.
Still it doesn't give me the SQL server instances (not all, but some) of some of the other computers on the network. So I figured that the best way should be to do the same thing this does with the local computer - that is to access teh registry of each of the computer on the network and see if each of the servers are there in those computers. So, can that be done? Can you lookup the registry of a computer in a network?
0
 
DhaestCommented:
One of the reasons that you don't find the sql-servers on other machines, can be that they are not completely correct configured (named pipes, ...) or because of security reason on those pc's...

public static string ConnectToRegistry (string servername) 
{ 
 
string regKeyToGet, keyToRead; 
 
ConnectionOptions oConn = new ConnectionOptions(); 
oConn.Username = "admin"; 
oConn.Password = "pass"; 
ManagementScope scope = new ManagementScope(@"//" + servername + @"/root/default", oConn); 
registry = new ManagementClass(scope, new ManagementPath("StdRegProv"), null); 
 
// Returns a specific value for a specified key 
ManagementBaseObject inParams = registry.GetMethodParameters("GetStringValue"); 
inParams["sSubKeyName"] = regKeyToGet; 
inParams["sValueName"] = keyToRead; 
ManagementBaseObject outParams = registry.InvokeMethod("GetStringValue", inParams, null); 
 
return outParams["sValue"].ToString(); 

Open in new window

0
 
DhaestCommented:
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now