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,