C# .NET - Get SQL Servers list - Asked By sunil juluru on 16-Feb-12 03:42 AM

How to get the registered Sql Servers list of Sql server Management studio in windows application using C#
kalpana aparnathi replied to sunil juluru on 16-Feb-12 03:44 AM
hi,

Here code for registered servers in C#

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;

namespace SMOTest
{
    class Program
    {
      static void Main()
      {
       RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
       String localserver = System.Environment.MachineName;
	   
       foreach (RegisteredServer rs in rsvrs)
       {
         Console.WriteLine(rs.ServerInstance.Replace(".",localserver)
                                            .Replace("(local)",localserver)
                                            .Replace("localhost",localserver));
       }
      }
    }
}

read more:http://www.sqldbatips.com/showarticle.asp?ID=45

Regards,
Somesh Yadav replied to sunil juluru on 16-Feb-12 04:04 AM
Enumerating Registered Servers

To enumerate servers registered in SQL Server Management Studio we can use the EnumRegisteredServers method of the SqlServerRegistrations class. One vast improvment over SQL-DMO is that there is no longer any need to use recursion to loop through the servers and server groups.

[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo.RegisteredServers
Imports Microsoft.SqlServer.Management.Smo

Module SMOTest

   Sub Main()
    Dim rsvrs as RegisteredServer() = SmoApplication.SqlServerRegistrations.EnumRegisteredServers()
      Dim localserver As String = System.Environment.MachineName
    For Each rs As RegisteredServer in rsvrs

     Console.WriteLine(rs.ServerInstance.Replace(".",localserver) _
                        .Replace("(local)",localserver) _
                        .Replace("localhost",localserver))

    Next
   End Sub

End Module

[C#]

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;

namespace SMOTest
{
    class Program
    {
    static void Main()
    {
     RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
     String localserver = System.Environment.MachineName;
       
     foreach (RegisteredServer rs in rsvrs)
     {
     Console.WriteLine(rs.ServerInstance.Replace(".",localserver)
                        .Replace("(local)",localserver)
                        .Replace("localhost",localserver));
     }
    }
    }
}
sunil juluru replied to Somesh Yadav on 16-Feb-12 04:05 AM
Sorry, I want to bind them to a drop down list, can you tell me the code in windows and not in console application
Web Star replied to sunil juluru on 16-Feb-12 05:09 AM
Yes you can easily get sql server infor using SMO, so you can do this in your window form only need to include these namespace on your from as
using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;

and then you need to get that list in your code and bind it with dropdwonlist as follows

       RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
       String localserver = System.Environment.MachineName;
	   
       foreach (RegisteredServer rs in rsvrs)
       {
	dd1SqlServer.Add(rs.ServerInstance.Replace(".",localserver)
                                            .Replace("(local)",localserver)
                                            .Replace("localhost",localserver)  ); }
DL M replied to sunil juluru on 16-Feb-12 05:22 AM
Hi..

check here previous post of Eggheadcafe ..

http://www.eggheadcafe.com/community/csharp/2/10116078/find-sql-servers-on-local-system.aspx
sunil juluru replied to Web Star on 16-Feb-12 05:26 AM
 am getting errors when i add those two namespaces.
Sreekumar P replied to sunil juluru on 16-Feb-12 05:31 AM
Hi,

You can use this code

Windows/ASP.NET web Applications

DataTable dt = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(true);
foreach (DataRow dr in dt.Rows)
{
  //Console.WriteLine(dr["Name"]);
  //Console.WriteLine("   " + dr["Server"]);
  //Console.WriteLine("   " + dr["Instance"]);
  //Console.WriteLine("   " + dr["Version"]);
  //Console.WriteLine("   " + dr["IsLocal"]);
  cmbServers.Items.Add(dr["Name"]);
}
Sreekumar P replied to sunil juluru on 16-Feb-12 05:32 AM
and Add Referance of  Microsoft.SqlServer.Smo from AddReference window.

and using
using Microsoft.SqlServer.Management.Smo;
Web Star replied to sunil juluru on 16-Feb-12 06:03 AM
You need to Add Referance of these two namespace before using it in you project