All About SQL Server DMOs

SQL Server can be managed programmatically using system-stored procedures, but Distributed Management Objects (DMOs) offer a more modern, object-oriented alternative. This article introduces SQL DMO in SQL Server 7.0 and SQL Server 2000, describes the SQL-DMO object model, and then focuses primarily on the database structure and the Job Server object model structure. The sample code and article demonstrate how to use various objects, such as the registry object, the configuration object, and the database object, to automate everyday administrative tasks such as scheduling and backups.

What is SQL Distributed Management Objects?

SQL-DMO (Distributed Management Objects) is a robust set of objects that grants programmatic access to the administrative and development components of SQL Server to developers and database administrators. DMO connects to a SQL Server instance via the MS SQL Server ODBC driver, and numerous SQL Server-installed stored procedures support DMO's functionality (and are required for Enterprise Manager to function). On a typical SQL Server installation, these SPS are installed using a master script called "sqldmo.sql." This script is in the "C: Program Files Microsoft SQL Server MSSQL Install" directory.

The script can be rerun to replace broken or missing procedures if necessary. You can execute various database administration and development tasks through the exposed properties and methods and access DMO from a COM development environment like Visual Basic or C++. The examples and references I use here will demonstrate that VB is my preferred development tool. If your development machine does not have an instance of SQL Server running, you may need the components necessary to access the SQL server through DMO. If you're developing in C++, you'll need the appropriate header files, but Microsoft was kind enough to package everything up in a single DLL with the appropriate name "Sqldmo.dll." Although I've never had to register the DLL manually, I anticipate that copying it to your development machine, registering it, setting a reference to the "Microsoft SQLDMO Object Library," and getting started coding should be reasonably straightforward.

Classification of Objects in SQL Server DMO

All the objects in the SQL-DMO hierarchy can be broadly classified into three groups: individual objects (such as databases, Tables, and Columns), collections (such as Databases, Tables, and Columns), and lists. List objects are similar to collections, except they don't support adding and removing elements. Typically you get a list as a return value from a method or property. Data is an integral part of businesses, and there’s great demand for administrators. Therefore, go through the SQL DBA career path if you want to set yourself up for this role.

Working with SQL-DMO Objects

All the objects in the DMO SQL Server hierarchy can be broadly classified into individual objects, collections, and lists. List objects are similar to collections, except they don't support adding and removing elements. Typically, SQL Server developers get a list as a return value from a method or property. For example, the following code fills a combo box control with server names.

Dim sqlApp As New SQLDMO.Application
    Dim NL As SQLDMO.NameList
    Set NL = sqlApp.ListAvailableSQLServers
    For index = 1 To NL. Count
        cboServers.AddItem NL.Item(index)
  Next 

The object model is similar to the hierarchy in Enterprise Manager. Underneath each server is a collection of databases. Tables, Stored Procedures, and Views all exist in collections under a database.

Here is an example of some code that would loop through the table's collection to return the name of each table.

'Assuming we have set up our connection.
Dim objDB As Database
Set objDB = objDMO.Databases("northwind")
Dim oTable As Table
For Each oTable In objDB.Tables
    MsgBox oTable.Name
Next

Another everyday use for SQL-DMO, which is undoubtedly requested in our forums regularly, is scripting database objects. Scripting is quite simple. Once we have a table object, as seen in the above example, we can call the script() method, and we are returned a string that contains the Create Table script for that object. We can set several scripting options for this method; for example, we can choose not to include any or only have constraints. 

Some code will generate a script for the employees' table in the Northwind database.

'Assuming we have set up our connection.
Dim objDB As Database
Set objDB = objDMO.Databases("northwind")
Dim oTable As Table
Set oTable = objDB.Tables("employees")
'Assuming we have a text box control named text1
Text1.Text = oTable.Script()

This article would only be complete with a bit of SQL code. Knowing that we can call COM objects in our T-SQL scripts using the sp OACreate procedure, it makes sense to put these two concepts to use. Without going into the details of how this works, a basic script to call DMO from T-SQL is:

declare @objDMO int
declare @objDatabase int
declare @resultCode int
declare @dbname varchar(200)
declare @tablename varchar(200)
declare @cmd varchar(300)
declare @temp varchar(8000)
Set @dbname = 'PUBS'
Set @tablename = 'Authors'
EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
if @resultcode = 0
print 'Created Object.'
Exec @resultcode = sp_OASetProperty @objDMO, 'login secure,' 'true.'
EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
if @resultcode = 0
print 'connected'
Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script'
Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
print @temp
EXEC @resultcode = sp_OADestroy @objDMO
if @resultcode = 0
Print 'destroyed object.'

How to install SQL DMO

All required SQL-DMO components are installed as part of an instance of Microsoft Sql Server or client. SQL-DMO is implemented in a single dynamic-link library (DLL). You may develop SQL-DMO applications on either a client or a server. No additional files are required when using an OLE Automation controller as a development platform. Application development using C and C++ requires the SQL-DMO header files.

Advantages of SQL DMO

The advantages of SQL DMO are:

1. It facilitates quick problem-solving turnaround from concept to implementation,
2. Little or no T-SQL required.  

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now

Conclusion

SQL Server uses distributed management objects(DMO) for a modern, object-oriented alternative for programmatic administration. In the above writeup, we have discussed SQL Server DMOs. We have discussed its uses, advantages, and how to install distributed management objects in SQL Server.