How to find all SQL Server Instances over a network from .NET

How to find all SQL Server Instances over a network from .NET

• Arbu

  Thanks! this is what i need!
  

• Armored77

  Here you go:

  /********** FIND ALL SERVERS - SQL-DMO **********/

  // Add a reference to the SQLDMO.DLL file;
  // usually located in <SQLServerInstallDrive>:\Program Files\Microsoft SQL Server\<Version>\Tools\Binn

  SQLDMO.ApplicationClass app = new SQLDMO.ApplicationClass();
  try
  {
      SQLDMO.NameList names = app.ListAvailableSQLServers();
      foreach (String strServer in names)
      {
          
      }
  }
  catch (Exception err)
  {
      // SQL-DMO Error...
  }
  finally
  {
      app.Quit();
      app = null;
  }

  /********** FIND ALL DATABASES - ADO.NET **********/

  String strConn = "Data Source=MYSERVER; User ID=sa; Password=password";
  using (SqlConnection sqlConn = new SqlConnection(strConn))
  {
      sqlConn.Open();
      DataTable tblDatabases = sqlConn.GetSchema("Databases");
      sqlConn.Close();

      foreach (DataRow row in tblDatabases.Rows)
      {
          String strDatabaseName = row["database_name"].ToString();
      }
  }

   

  
  
  

• kstephan

  You can use SQL-DMO for that:

   // Add a reference to the SQLDMO.DLL file;
  // usually located in <SQLServerInstallDrive>:\Program Files\Microsoft SQL Server\<Version>\Tools\Binn

  SQLDMO.ApplicationClass app = new SQLDMO.ApplicationClass();
  try
  {
      SQLDMO.NameList names = app.ListAvailableSQLServers();
      foreach (String strServer in names)
      {
          
      }
  }
  catch (Exception err)
  {
      // SQL-DMO Error...
  }
  finally
  {
      app.Quit();
      app = null;
  }

  
  
  

• Coconut1

  Finally i found the managed code for do this! Is a new feature in .NET 2.0:

  (From .NET 2.0 Doc.)

  VB.NET Version

  Imports System.Data.Sql

  Module Module1
    Sub Main()
      ' Retrieve the enumerator instance and then the data.
      Dim instance As SqlDataSourceEnumerator = _
       SqlDataSourceEnumerator.Instance
      Dim table As System.Data.DataTable = instance.GetDataSources()

      ' Display the contents of the table.
      DisplayData(table)

      Console.WriteLine("Press any key to continue.")
      Console.ReadKey()
    End Sub

    Private Sub DisplayData(ByVal table As DataTable)
      For Each row As DataRow In table.Rows
        For Each col As DataColumn In table.Columns
          Console.WriteLine("{0} = {1}", col.ColumnName, row(col))
        Next
        Console.WriteLine("============================")
      Next
    End Sub
  End Module

  C# Version

  using System.Data.Sql;

  class Program
  {
    static void Main()
    {
      // Retrieve the enumerator instance and then the data.
      SqlDataSourceEnumerator instance =
        SqlDataSourceEnumerator.Instance;
      System.Data.DataTable table = instance.GetDataSources();

      // Display the contents of the table.
      DisplayData(table);

      Console.WriteLine("Press any key to continue.");
      Console.ReadKey();
    }

    private static void DisplayData(System.Data.DataTable table)
    {
      foreach (System.Data.DataRow row in table.Rows)
      {
        foreach (System.Data.DataColumn col in table.Columns)
        {
          Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
        }
        Console.WriteLine("============================");
      }
    }
  }

  With this the user does not need to have SQLDMO.dll in his system.

  
  

• r2d2

  Thanks, but i still in the same problem, i not need to know the databases in a server, i need know the name of all instances of sql in my network:

  sample:

  SQLPRODUCTION

  SQLPRODUCTION\TEST

  MYCOMPUTER

  TESTMACHINE\DEVELOPMENT

  The purpose is to make a "Add Connection" dialog clone from de VS IDE.

  
  
• How to find all SQL Server Instances over a network from .NET