SQL Server Management Objects (SMO) : part 1

sun_kangane's Avatar author of SQL Server Management Objects (SMO) : part 1
This is an article on SQL Server Management Objects (SMO) : part 1 in SQL Server.
Rated 5.00 By 1 users

What is SMO?

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

SMO Overview

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.

  1. The user applications that control the SQL Server management tasks might have to be simplified to meet the needs of new users and to reduce training costs.
  2. You might have to create customized SQL Server databases,
  3. Create an application for creating and monitoring the efficiency of indexes
  4. An SMO application might also be used to include third-party hardware or software seamlessly into the database management application. Like priter, accesing web.


The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Compared to SQL-DMO, SMO increases performance, control, and ease of use. Most SQL-DMO functionality is included in SMO, and there are various new classes that support new features in SQL Server. The object model is intuitive and uses SQL-DMO terminology, where it is possible, to help transfer your skills.

Features of SMO

  • Cached object model and optimized object instance creation. Objects are loaded only when specifically referenced. Object properties are only partially loaded when the object is created. The remaining objects and properties are loaded when they are referenced directly.(Daynmic Loading and Binding ) this will provide memory manegment and efficent use of SMO objects.
  • Batched execution of Transact-SQL statements. Statements are batched to improve network performance.
  • Capture Transact-SQL statements. Allows any operation to be captured into a script. Management Studio uses this capability to script an operation instead of executing it immediately.
  • Management of SQL Server services with the WMI Provider (Windows Management Instrumentation). SQL Server services can be started, stopped, and paused programmatically.
  • Advanced Scripting. Transact-SQL scripts can be generated to re-create SQL Server objects that describe relationships to other objects on the instance of SQL Server.
  • Use of Unique Resource Names (URNs). A URN allows you to create instances of and reference SMO objects.

Preparing for SMO use

  1. SMO Syntax Conventions: SQL Server Management Objects (SMO) typographical conventions are based on those used in Microsoft Visual Studio .NET reference materials.( Transact-SQL statements, macro names, and terms used at the operating system level; file names in text.)
  2. System Requirements: SQL Server Management Objects (SMO) uses the Microsoft System.Data.SqlClient object driver to connect to and communicate with instances of SQL Server. i.e SQL Server Native Client, which is included with SQL Server and the .NET Framework 2.0. , Visual Studio (2005 or 2008).
Programming Languages:
  1. Visual Basic .NET
  2. Visual C# .NET

Creating SMO Programs

General programming of SQL Server Management Objects (SMO) objects includes the common areas that all objects share, such as running methods, setting properties, and manipulating collections.

Stapes Creating a Visual C# SMO project in Visual Studio.NET
  1. Start Visual Studio 2005 or 2008.
  2. On the File menu, click New Project. The New Project dialog box appears.
  3. In Project Types dialog box, select Visual C# Projects. In the Templates pane, select Windows Application.
  4. (Optional) In Name, type the name of the new application
  5. Select the Visual C# application type. For the examples that follow select Console Application.
  6. On the Project menu, select Add Reference. The Add Reference dialog box appears.
  7. Select Browse, locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ and select the following files:
    1. Microsoft.SqlServer.ConnectionInfo.dll
    2. Microsoft.SqlServer.Smo.dll
    3. Microsoft.SqlServer.SmoEnum.dll
    4. Microsoft.SqlServer.SqlEnum.dll
  8. Add the additional SMO assemblies if you are specifically programming Service Broker or WMI:
    1. Microsoft.SqlServer.ServiceBrokerEmum.dll
    2. Microsoft.SqlServer.WmiEmum.dll
  9. Click Open.
  10. On the View menu, click Code.-Or-Select the Program1.cs [Design] Windows and double click the windows form to display the code window.
  11. In the code, before the namespace statement, type the following using statements to qualify the types in the SMO namespace:
    1. using Microsoft.SqlServer.Management.Smo;
    2. using Microsoft.SqlServer.Management.Common;
  12. SMO has various namespaces under Microsoft.SqlServer.Management.Smo, such as Microsoft.SqlServer.Management.Smo.Agent. Add these namespaces as needed.
  13. You can now add your SMO code.

in next part i will tell about how to write SMO program..........................
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Nomination for article of the month for December 2008 Started.
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Its among the Article of the month and votes can make it win, so start Voting for article of the month for December 2008
sun_kangane's Avatar, Join Date: Mar 2007
Go4Expert Member
Originally Posted by shabbir View Post
Its among the Article of the month and votes can make it win, so start Voting for article of the month for December 2008

I am happy ! thanks for Nomination!!!!!!!!!!!!!!!!
LenoxFinlay's Avatar
Can I make Oracle to automatically trigger SQL Server to run an sql command?
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Originally Posted by LenoxFinlay View Post
Can I make Oracle to automatically trigger SQL Server to run an sql command?
Using Oracle trying to execute something in SQL Server is something like Stay in US and Rule Taliban
technica's Avatar
Ambitious contributor
Nice article. Where is the Part II of the article?