Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  An introduction to SQL Server Management Objects
An introduction to SQL Server Management Objects
by David Jeavons | Published  01/02/2007 | SQL Server | Rating:
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 

View all articles by David Jeavons...
Finding and connecting to an SQL Server

Before we can perform any operations against an SQL Server we must first connect to an instance of an SQL Server. To do this, we can either connect directly to a known instance or we can browse for all available SQL Servers that are on the network. Fortunately, retrieving a list of available SQL Servers is an extremely simple process; however, we must first make a reference to the Microsoft.SQLServer.SMO, Microsoft.SQLServer.ConnectionInfo and Microsoft.SQLServer.SqlEnum libraries and add the following Imports statement to our code file:

    1 Imports Microsoft.SqlServer.Management.Smo

    2 Imports Microsoft.SqlServer.Management.Common

 

To retrieve a list of available SQL Servers we can use the EnumAvailableSqlServers shared method of the SMOApplication object:

 

    1         Dim availableServers As DataTable = SmoApplication.EnumAvailableSqlServers

 

This method has two overloads, the first allows you to specify whether you want to enumerate through all servers on the network or only the local instance on your machine and the second allows you to specify the name of a computer where the instances of SQL Server are installed. An interesting point to note is that if you are not connected to a network then the method will simply look for the local instance of SQL Server regardless of the overloads you have specified. For demonstration purposes, this article will assume that you are connected to your local instance, although I will show you the code required to connect to any instance of SQL Server that you have access too.

 

Once this method call has finished, you will be returned a DataTable containing a list of all SQL Server instances found. Note, that if you are connecting to a local instance only then the DataTable will be empty as it does not return details for the local instance. The DataTable contains a column called Name which you can use when specifying the name of the SQL Server that you want to connect to.

 

To connect to the SQL Server of interest you need to create a server object and specify which SQL Server you are connecting to and how you wish to connect (SQL Server or Windows Authentication). The following code snippet demonstrates how to connect to a local instance of SQL Server using SQL Server authentication:

 

    1         Dim sqlServer As New Server()

    2 

    3         With sqlServer.ConnectionContext

    4             .ServerInstance = "(local)"

    5             .LoginSecure = False

    6             .Login = "username"

    7             .Password = "password"

    8             .Connect()

    9             .Disconnect()

   10         End With

 

If you wanted to connect to a different SQL Server instance then modify the ServerInstance property to the name of the SQL Server instance you want to connect too.

 

In the above example, the LoginSecure property is set to False to indicate that SQL Server authentication is to be used. By default, this property is set to True so if you wanted to use Windows Authentication to connect to the SQL Server instance then you can simply remove the LoginSecure, Login and Password properties:

 

    1         Dim sqlServer As New Server()

    2 

    3         With sqlServer.ConnectionContext

    4             .ServerInstance = "(local)"

    5             .Connect()

    6             .Disconnect()

    7         End With

Sponsored Links