Overview of SMO

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

SMO in SQL Server 2008 is compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. SMO in SQL Server 2005 is compatible with SQL Server 2000, SQL Server 2005. So, you can easily manage a multi-version environment with a higher version of SMO.

Install SMO

Microsoft SQL Server 2008 Management Objects Collection (a component of Feature Pack for Microsoft SQL Server 2005/2008)

The Management Objects Collection package includes several key elements of the SQL Server 2008 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2008.

By default, the SMO assemblies are installed in the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies, you can track this folder to see whether SMO installed successfully.

Call SMO in C#

These are the minimum files that are required to build an SMO application, and they are under the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Microsoft.SqlServer.SqlEnum.dll

Here is a demo of script all jobs in sql server

   1:  Server server = new Server(ServerName);

   2:  JobServer server2 = server.JobServer;

   3:  foreach (Job job in server2.Jobs)

   4:  {

   5:      WriteToFile(job, PathToWriteScripts);

   6:  }

In my last post Call SQLCMD to run SQL script via C#, we know how to call SQLCMD via C#. But in most scenarios, we also need the exit code(%ErrorLevel%) or the sub SQLCMD thread. It is simple, use ‘-b’ in sqlcmd can do this!

So, just add a –b parameter when call the SQLCMD!

Here are more parameters in error reporting,

Usually, we need to call some sql script to update our database in our application(C#), and it is a good way to call SQLCMD to execute the sql scripts. One important reason is that our scripts always contains the GO statement which is is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. Since the SQLCMD and SQL Server Management Studio will do the same thing, the scripts edited by SQL Server Management Studio will be 100% supported by SQLCMD.

Here is a block of code to call SQLCMD:

string tmpFile = Path.GetTempFileName();

string argument = string.Format(@" -S {0} -d {1} -i ""{2}"" -o ""{3}""",
    ServerName, DatabaseName, fileName, tmpFile);

// append user/password if not use integrated security
if (!connection.IsIntegratedSecurity)
    argument += string.Format(" -U {0} -P {1}", User, Password);

var process = Process.Start("sqlcmd.exe", argument);
process.StartInfo.UseShellExecute = false;
process.StartInfo.CreateNoWindow = true;
process.Start();
while (true)
{
    // wait for the process exits. The WaitForExit() method doesn't work
    if (process.HasExited)
        break;
    Thread.Sleep(500);
}

GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

GO Statement must be written in new line as it is not T-SQL command. T-SQL statement can not occupy the same line as GO. GO statement can contain comments.

select * from tableA
go
insert into tablea(MyValue) values(‘aaa’)
go
select * from tableA
go

The above sql script for example, will give you a ‘Incorrect Syntax near GO’ exception. the solution to this is remove the ‘GO’ statement.

Some of the big improvements in EF4 include:

EF4 enables you to:

• Develop without ever having to open a designer or define an XML mapping file
• Define your model objects by simply writing “plain old classes” with no base classes required
• Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything
• Optionally override the convention-based persistence and use a fluent code API to fully customize the persistence mapping