Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   SQL Server Management Objects (SMO) : part 1 (http://www.go4expert.com/articles/sql-server-management-objects-smo-part-1-t15488/)

sun_kangane 14Dec2008 23:02

SQL Server Management Objects (SMO) : part 1

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 6Jan2009 12:25

Re: SQL Server Management Objects (SMO) : part 1
Nomination for article of the month for December 2008 Started.

shabbir 19Jan2009 09:39

Re: SQL Server Management Objects (SMO) : part 1
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 19Jan2009 11:36

Re: SQL Server Management Objects (SMO) : part 1

Originally Posted by shabbir (Post 41647)
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 27Jun2009 12:49

Re: SQL Server Management Objects (SMO) : part 1
Can I make Oracle to automatically trigger SQL Server to run an sql command?

shabbir 27Jun2009 15:03

Re: SQL Server Management Objects (SMO) : part 1

Originally Posted by LenoxFinlay (Post 50582)
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 24Dec2009 14:04

Re: SQL Server Management Objects (SMO) : part 1
Nice article. Where is the Part II of the article?

All times are GMT +5.5. The time now is 07:37.