SQL Server 2005 : Working with SQL Server Management Objects in Visual Studio (part 1) - Iterating Through Available Servers

1/6/2013 6:21:49 PM

To get started working with SMO, you must first set a reference in the Visual Studio project to the SMO assembly by choosing Project, Add Reference and selecting the Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo components, as shown in Figure 1.

Figure 1. Setting a reference to SMO

You then add two using statements for the two namespaces that you will use most often. In C#, it looks like this:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

In Visual Basic, it looks like this:

Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo

Next you will want to work with SMO objects, the first being the ServerConnection object. This object represents a connection to an SQL Server instance. It lets you connect to a server and assign that connection to an SMO Server object. The Server object represents an actual SQL Server instance. The object allows you to retrieve the database server’s properties and perform tasks such as attaching a database. To help you understand how to use these objects, we will create a simple application to connect to an SQL Server instance using the ServerConnection object and then use a Server object to retrieve the SQL Server version, edition, and name of the instance. This application will ask the user for a user ID and password or allow the use of integrated security (as shown in Figure 2).

Figure 2. Simple SMO application

The code in Listing 6-1 shows how to connect to an SQL Server instance using either integrated security or SQL Server security. The results are shown in Figure 3.

Figure 3. Results of the simple SMO application

Listing 1. Connecting to an SQL Server database via SMO
      private void btnConnect_Click(object sender, EventArgs e)
            //ServerConnection object used to connect
            ServerConnection conn = new ServerConnection();
            //pass in the name of the server/instance
            conn.ServerInstance = txtServer.Text.ToString();

            //determine if integrated security is set
              if (chkSSPI.Checked == false)
              //using sql security
                  conn.LoginSecure = false;
                conn.Login = txtUID.Text.ToString();
                  conn.Password = txtPwd.Text.ToString();

            //try to connect and return server information
                 Server svrLocal = new Server(conn);
                //pop up the message box to the user here
                //get the info via the Information property
                      MessageBox.Show("You are not connected to: " + svrLocal.Name +
System.Environment.NewLine +
                           " Edition Info: " + svrLocal.Information.Edition +
System.Environment.NewLine +
                         " Version Info: " + svrLocal.Information.Version,"SMO Demos");

               catch (SmoException exSMO)
                  //catch SMO specific exceptions

              catch (Exception ex)



When you reference a local server with an instance name, you can use the syntax (local)\instance_name instead of the less flexible server_name\instance_name.

1. Iterating Through Available Servers

Depending on the type of functionality you need when performing your administrative tasks, you might have to programmatically enumerate a list of or iterate through available SQL Server instances. To perform this task in SMO, you can use the SmoApplication object’s EnumAvailableSqlServers method, passing in a Boolean value to tell SMO whether to look only locally. The EnumAvailableSqlServers method returns a DataTable with the results of the enumeration filled with lots of properties. The following code calls a reusable method in a helper class called ListAllKnownInstances, as shown in Listing 6-2. ListAllKnownInstances searches the network for all the SQL Server instances it can “see” by using EnumAvailableSqlServers and returns the resulting DataTable. Our code then takes that DataTable and binds it to a DataGridView.

private void button1_Click(object sender, EventArgs e)
    DataTable dtList = SMOUtilities.ListAllKnownInstances();
    dataGridView1.DataSource = dtList;

Listing 2 makes a call to the EnumAvailableSqlServers method and displays the results in a DataGrid.

Listing 2. Enumerating available servers
public static DataTable ListAllKnownInstances()
    //this method will return a DataTable
    //based on all of the servers available
    //this method can take some time depending on your network
    //will connect to SQL Server 2000 machines too
    DataTable dtServers = new DataTable();

        //Obtain a list of all SQL Server available
        //this method returns a DataTable
        dtServers = SmoApplication.EnumAvailableSqlServers(false);

    catch (SmoException exSMO)

    catch (Exception ex)

    return dtServers;


Figure 4 shows the results of running the code in Listing 2.

Figure 4. Simple enumeration of available servers

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone