This article includes the source of stored procedures that will:

1. List all available servers
2. Enumerate all database on a Server
3. Create a new database on any available server
4. Copy an entire database within a server or between servers
5. Copy selected tables or stored procedures between databases
6. Write out a complete build script for a database
7. Write out build scripts for every database on a server
8. Write out a series of source files for the objects in a database in a suitable format for source-control systems
9. List the database roles and the users assigned to them, for a particular database
10. Checks the jobs on a server for their success or otherwise and accesses the history records

Its main intention is to show how easy it is to use DMO (and its successor, SMO) from within SQL Server, and how any COM object that does not rely on events can be used.

                                                                                ===+===

What follows in this article provides a taster of the sort of things you can do with DMO, showing the code to carry out the first three tasks in the above list.

All of the other procedures, with full source code, are freely available from the following link:

ACCESS ALL DMO PROCEDURES AND SOURCE CODE

                                                                                ===+===

When Microsoft made its decision to remove Distributed Management Objects (DMO) from SQL Server 2005, they had underestimated the extent to which it is used. DMO was the “secret” application behind Enterprise Manager and, with it, one could automate almost anything that one could do by hand with EM.

Eventually, Microsoft bowed to pressure and released “Microsoft SQL Server 2005 Backward Compatibility Components” which included the DMO. However, MSDN gives dire warnings that the DMO won’t be supported much longer. SQL Server 2005 supplements SQL-DMO with two .NET based object libraries: SQL Server Management Objects (SMO) and Replication Management Objects (RMO). These components are more powerful than the DMO, and can be used with SQL 2000 and v7. Hopefully, a subsequent article will cover the SMO.

The application we originally knew as SQLOLE has survived two name-changes, but the fundamental concepts remain the same. DMO presents a simple programmatic interface via COM, so it can be used in a number of languages, including scripting Shell, PHP, VB, C# and even within SQL Server itself.

In this article, we’ll be tackling the use of the DMO in SQL Server. This might seem an odd thing to want to do, but, in fact, it allows you to perform administrative functions that simply cannot be done in any other way. One must also explain why it is worth tackling a subject that has been the subject of many previous articles, and book chapters. The reason is that so many of the examples are poorly written, and make the whole process seem far more complex than it really is.

Getting started with DMO: listing all available servers

Busy DBAs can’t afford to perform routine processes manually. There just isn’t time. Everything has to be scripted, automated and scheduled, with a system of checks and alerts. Whenever I find myself doing something with SSMS, or EM, more than a couple of times, I script the task and then automate it. Quite often, the scripting is done using SQL Server.

The DMO COM interface consists of:

• sp_OAGetProperty which gets the value of a property.
• sp_OASetProperty which sets the value of a property.
• sp_OAMethod which calls a method.
• sp_OAGetErrorInfo which obtains the most recent error information.

There is no way to handle events from COM objects. The best way of getting started with DMO is via a simple example. This procedure creates a result with the names of all the available servers, just as appears in the drop-down list in Enterprise Manager when registering a server:

Check it out by simply executing exec spAllAvailableServers (note that you may need to enable OLE Automation objects, via Surface Area Configuration for Features) Notice that there are no GOTO statements that one sees so often in such code. Also there are no cursors. These aren’t necessary and they bulk up the code. This is a vital factor when you are creating more complex DMO procedures. The first step when using an OLE Automation object in Transact-SQL is to call the sp_OACreate system stored procedure to create an instance of the object in the address space of the instance of the Database Engine. In this case, it is the SQLDMO application object. Then we just get from it the list of available SQL Servers. If an automation error occurs, the code drops through to the error handler and thence out, passing the HRESULT back to the calling procedure, but still providing an empty result.

Enumerating all databases on a Server

So what about adapting an example application from VB? Here is one that enumerates all the databases on a server, and gives their size in Mb (from Rick Dobson 12.06.2000): 

We can use this code as the basis for a stored procedure, which will do exactly the same thing:

Of course, in a working version, you’d want the available space in MB (SpaceAvailableInMB), the Status (e.g. Standby, Normal, Recovering etc) and a host of other information useful to the DBA. It is a simple matter of exploring the DMO Object model on MSDN and seeing what’s available.

Create a database on any server

For this, we can take the MSDN example for creating a database as our basis:

A simple conversion of this routine allows us to create a database on any of the servers within our ‘stamping ground’:

Hopefully, this has provided a taster of the power of DMO for automating common SQL server admin tasks. Again, to access the rest of the DMO procedures that accompany this article, just click on the link below:

ACCESS MORE DMO PROCEDURES