Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to SQL-DMO

By Sean Burke,

Introduction to SQL-DMO

What is it?

SQL-DMO (Distributed Management Objects) is a powerful collection of objects that gives developers and DBAs programmatic access to the administrative and development components SQL Server.  DMO uses the MS SQL Server ODBC driver to connect to an instance of SQL Server, and there are numerous stored procedures installed with SQL Server that support the functionality of DMO (they are also required for Enterprise Manager to work).  These sps are installed from a master script called "sqldmo.sql" that can be found in the " C:\Program Files\Microsoft SQL Server\MSSQL\Install "directory on a standard SQL Server Installation.  The script can be re-run if needed to repair damaged or missing procs.

You can access DMO through a COM development environment like Visual Basic or C++, and execute a wide variety of database administration and development tasks through the exposed properties and methods.  VB is my development tool of choice, and that will be reflected in the examples and references I use here.  If you don't have an instance of SQL Server running on your development machine, chances are you don't have the necessary components to access SQL server through DMO.  Not to worry - the kind folks at Microsoft were nice enough to wrap everything up in a single DLL, aptly named "Sqldmo.dll" (you'll need the appropriate header files if you are developing in C++).  I've never actually had to register the DLL on its own before, but I expect that it should be fairly simple to copy it onto your development machine, register it, set a reference to the "Microsoft SQLDMO Object Library", and start coding away.

What is it good for?

Two things that I like about DMO:  1)  It facilitates quick problem-solving turn around from concept to implementation, and 2) Little or no T-SQL required.  As much as I love SQL Server, I'm not a big fan of complex T-SQL for administration, and I am a huge fan of solving problems quickly and efficiently (Yukon cannot get here fast enough).  DMO has proven invaluable in our environment for quickly automating maintenance tasks that require scores of routines to be checked against nearly 300 databases.  It allows you to quickly get into the guts of SQL server do some quick integrity checks, all from the comfort of your favorite development environment.  If there is a maintenance routine that you find yourself firing up on an increasingly frequent basis, then you may want to look at DMO to help take on the drudgery.

I have also seen a few home-grown applications that are designed to augment or replace the enterprise manager, and DMO is a core part of those programs.  If you have the time and the inclination, you can use DMO to build an application or utility that shields your user from the intricacies of database development and SQL syntax, enabling them to create database objects and perform a variety of administration tasks without the benefit of SQL client tools.  The merits of doing so are beyond the scope of this article, and should not be taken lightly.

The DMO Object Model

DMO was put together just like any other good object design; it has its top level objects, properties, methods, and collections of other objects.  These sub-objects in turn have their own properties and methods, and in some cases (like backups) have that ability to raise events.  I have a graphic that outlines the object model hierarchy, and it should give you a good idea of what DMO is capable of regarding SQL Server functionality.  It is promoted as being able to handle any task you need to perform in the SQL server application, and I have yet to run into a situation where that wasn't the case.  Let me know if you have, and we'll post your case in the discussion area for input from the SSC community.

The entry point into DMO generally starts with the SQLServer object.  I say "generally" here because for most of what you will need to get accomplished with DMO, you will know which instance of SQL server you want to use.  In the case that you would want to be able to chose which instance to use, there is an object that is one level higher than the SQLServer object - the Application object.  This object has a number of useful methods and properties, not the least of which is the ability to quickly provide a list of available instances of SQL Server on the network (I have left out the clean-up and error handling for simplicity):

Function listServers(vControl As Object)

 

    Dim oApp As SQLDMO.Application

    Dim oNames  As SQLDMO.NameList

 

    Set oApp = New SQLDMO.Application

    Set oNames = oApp.ListAvailableSQLServers()

 

    For Each oName In oNames

        vControl.AddItem oName

    Next

 

End Function

 

This example takes a parameter that represents the name of a listbox control on a form, creates an application object, and executes the ListAvailableSQLServers method which returns a NameList object.  The NameList object holds an enumerated list of all of the "network-visible" instances of SQL Server.  Looping through this list, each is added to the listbox control, and the user can then select which server to use from the listbox.  The usefulness of this function is extended by the next piece of code, one which gets a listing of the databases for a target server.  The server name that was picked from the list generated by the previous function is now passed to the user-defined listDatabases function, along with a name reference to another listbox control on the form: 

Function listDatabases(vServer As String, vControl As Object)

 

Dim oServer As SQLDMO.SQLServer

Set oServer = New SQLDMO.SQLServer

 

oServer.LoginSecure = True

oServer.Connect vServer

 

For Each oDatabase In oServer.Databases

    vControl.AddItem oDatabase.Name

Next

 

End Function

 

This illustrates two very important aspects of the SQLServer object:  the log in methodology, and the Databases collection.  Here I have logged in using Windows Authentication Mode by setting the LoginSecure property to TRUE.  If you want to use the SQL login, set this property to false (or leave the statement out altogether) and supply the user name and password.  The Connect method takes three parameters:  Server name, login, and password.  Here's an example using the SQL login: 

oServer.Connect vServer, "sa", "theSApassword"

In both cases, I passed the name of the SQL Server instance in the variable vServer.  If you opt for the SQL login, you do not need to set the LoginSecure property, but you will have to supply a user name and password.  These are not required when using Windows Authentication. 

Conclusion

This article barely scratches the surface of DMO, but hopefully has given you enough information to begin exploring it on your own.  The best recommendation I can give you is to create a test project and just start trying it out, examining the properties of different objects, looping through the collections, and testing the available methods - but please don't do it on a production database server.  If you have any questions or suggestions for uses of DMO, or have run into any problems with it (or my characterization of it here), please follow the link to the discussion area for this article, or the DMO discussion area, and post your message there.  I'll be writing more in-depth articles on DMO in the near future, so if you have any ideas you would like to see on the site, please send me an email and I'll do what I can to accommodate your request.

Total article views: 11293 | Views in the last 30 days: 2
 
Related Articles
BLOG

SQL server could not start cannot find object or property (0×80092004)

SQL server could not start cannot find object or property (0×80092004) Recently I got a call from m...

BLOG

Remember OBJECTPROPERTY(..) when investigating objects.

The object property function appears to be often overlooked. This function will tell us about any s...

ARTICLE

SSIS – Transfer SQL Server Objects Debugged

A new SSIS developer finds a problem with the Transfer SQL Server Objects task. Join new author Step...

ARTICLE

Custom SSMS Shortcuts for ETL Developer. Part 2: Extended Properties

With the following technique, you can quickly read information stored in the extended properties of ...

ARTICLE

Extended Properties Introduction

Part one of a four part series intent on demystifying and making more accessible SQL Server extended...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones