Sunday, August 14, 2011

SMO - Connecting to the SQL Server

When you want to connect to SQL Server 2005 (or later version), you can user SQL Server SMO. To get started with SMO, first you must add references in Visual Studio. In the Add Reference window, select:
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Mircorost.SqlServer.ConnectionInfo
When the references are added, you must add two using statements for these namespaces:
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
Now you can start working with SMO. Before doing restore and backup tasks, you must connect to the server. This connection is provided by the ServerConnection object. It lets you connect to the server and assign that connection to the Server object. The Server object represents an instance of SQL Server. In SMO programming, the Server object determines the connection to a physical SQL Server installation.
ServerConnection conn = new ServerConnection("INSTANCE", 
                            "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}
This example shows you how to connect to SQL Server using a user name and password. Alternatively, you can use Windows Integrated Authentication:
ServerConnection conn = new ServerConnection();
conn.ServerInstance = "INSTANCE";
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}
For disconnecting from SQL Server, we use the function Disconnect() of the ServerConnection class. The ServerConnection object is represented by Server.ConnectionContext, and alternatively you can disconnect from SQL Server by calling:
Server.ConnectionContext.Disconnect();

2 comments:

  1. Until today all my PowerShell scripts would connect to the server using the Server class as myself using Windows Authentication.

    Today I needed specify a SQL server login with a SecureString password. Your pointer towards the ServerConnection class was exactly what I needed. Thanks!

    ReplyDelete
  2. good seo information i like it this blog. complete course in digital marketing training, click perfect with live projects.


    ReplyDelete