icrosoft® SQL Server™ provides an ever-growing set of system stored procedures for automating administrative chores such as creating databases and tables, running unattended backup jobs, and more. However, in the past you had to be proficient with T-SQL programming to take full advantage of this capability. All that has changed with the introduction of the Distributed Management Objects (DMO). They let any programmer use his language of choice to perform these tasks.
      SQL-DMO is a set of nearly 150 distinct objects that cover virtually every facet of SQL Server 7.0 and SQL Server 2000 management. The object model has full support for dual interfaces, so you can use it from virtually any language, including Visual Basic®, C++, VBScript, JScript®, Windows® Script Host (WSH), and ASP scripts. Along with this article I've provided several code snippets, as well as complete applications that illustrate how to programmatically retrieve configuration settings, create new databases, apply T-SQL scripts, and create and schedule backup jobs. I've written much of the code in Visual Basic, but you can easily apply these concepts to any language you like.

SQL-DMO Overview

      From a physical point of view, the DMO object model is implemented in SQLDMO.DLL. The ancillary SQLDMO.RLL file contains all the localized resources. In SQL Server 7.0, you can find this RLL file in the \Mssql7\Binn\Resources\xxxx directory (assuming you're using the default install path), where xxxx is the decimal value of the language identifier (for example, 1033 for U.S. English). In SQL Server 2000, the default path is the C:\Program Files\ Microsoft SQL Server\80\ Tools\Binn\Resources\xxxx directory. The standard SQL Server setup routine installs these files, so you don't need to take any additional steps to activate this feature. The setup program also runs the SQLDMO.SQL T-SQL script to install a few system procedures that are needed by the DMO subsystem. You can run this script yourself from the \Mssql7\Install directory (SQL Server 7.0) or Microsoft SQL Server/MSSQLsMAINSQLSVR/Install Directory (SQL Server 2000) if you believe that these procedures have been deleted or altered. DMO is also installed as part of the client tools setup should you need to install it on client workstations. The REDIST.TXT file in the root of the SQL Server installation CD explains how to redistribute and install SQL-DMO.
      The SQLDMO.DLL component talks to SQLSVC.DLL (the database abstraction module), which talks to ODBC32.DLL, which in turn talks to the SQL Server ODBC driver. You need at least version 3.70 of the ODBC driver, which is the version distributed with SQL Server 7.0. If an alias has been defined and you use the alias name as the server name, SQL-DMO uses the alias to find the ODBC data source. SQLDMO.DLL uses the default protocol defined in the Client Network Utility and has no ability to overwrite the network protocol.

Top-level SQL-DMO Objects

      The DMO object model is surprisingly easy to use, in spite of the large number of objects it comprises, in that its structure resembles the object hierarchy in the SQL Server Enterprise Manager. The Application object is the entry point of the hierarchy; below it you'll find the ServerGroup object, then the SQLServer object. Figure 1 shows all the top-level objects in the model.


Figure 1 The Application Object

There are three important subtrees for the SQLServer object: the Databases subtree (which comprises all the objects to create and manage databases, tables, indexes, and so on), the JobServer subtree (containing all the objects for defining jobs and their schedules), and the Replication subtree (which lets you replicate databases, define publishers and subscribers, and so on). In this article I'll focus only on the Database and JobServer subtrees.

Working with SQL-DMO Objects

      All the objects in the SQL-DMO hierarchy can be broadly classified in three groups: individual objects (such as Database, Table, and Column), collections (such as Databases, Tables, and Columns), and lists. List objects are similar to collections, except they don't provide support for adding and removing elements. Typically you get a list as a return value from a method or property. For example, the following code fills a combobox 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
  

      In SQL Server 7.0 this code returns running servers that listen on Named Pipes, are located in the same domain, and are running Windows NT. (Since this is implemented using a NetBIOS broadcast, your router infrastructure may even narrow the list further.) (Note that this code will not report Windows 9x-based servers running SQL Server because they are not listening on Named Pipes.) And in SQL Server 2000 the discovery is done using a TCP UDP broadcast (in addition to the existing NetBIOS), so this code also returns running TCP servers. In addition, the code will return the server on the local machine if it is running.
      In the following sections I'll show you how to use the SQLServer object to perform some common administration tasks.

Connecting and Disconnecting

      To connect to a specific instance of SQL Server, you create a standalone SQLServer object, optionally set its LoginTimeout to a suitable value, and then call its Connect method, as you can see in the following code:

    Dim SQLServer As New SQLDMO.SQLServer
  

    SQLServer.LoginTimeout = 10
  

    If UseNTAuthentication Then
  

        ' Windows NT security
  

        SQLServer.LoginSecure = True
  

        SQLServer.Connect strServer
  

    Else
  

        ' SQL Server security
  

        SQLServer.Connect strServer, strLogin, strPassword
  

    End If
  

The exact syntax depends on whether you are connecting using Windows NT security or SQL Server security.
      In the previous code, strServer is the name of server, and strLogin and strPassword are the login name and password requested when logging through SQL Server security. Bear in mind that if the login fails, you get a runtime error. Therefore, a real application should always protect these statements with an error handler.
      Unlike the ADO Connection object, the SQLServer object doesn't automatically close the connection when it goes out of scope, so you'll leave a connection open until you get a timeout if you don't disconnect explicitly. It is very important that you disconnect from the server running SQL Server (with SQLServer.Disconnect) when you've completed your job because the metadata cache built by SQL-DMO will not be destroyed until you disconnect (although SQL Server 2000 tries to release objects out of the metadata cache sooner, if possible). Therefore, all objects should be destroyed with

  set oDatabase = Nothing
  

to prevent DMO from holding onto resources. The metadata cache is maintained per each process that uses SQL-DMO (in other words, the metadata cache is local to the process of its memory address space and cannot be shared between two processes on the same machine).
      You can also use the SQLServer object to start the Microsoft SQL Server service and connect to it in one operation, as well as pause, continue, and stop the service (see Figure 2). The Visual Basic-based example, stoplite, which you'll find in the code download also demonstrates how to do this (see the link at the top of this article). To query the current status of the service, you can use the SQLServer.Status property.
      Note that the SQLServer object directly controls only the MSSQLServer service. To start and stop the SQLServerAgent service that is in charge of running scheduled jobs, you must use the JobServer object:

  SQLServer.JobServer.Start
  

  SQLServer.JobServer.Stop
  

      To configure services in SQL Server so they start automatically at boot time, set one or more properties of the Registry object:

  SQLServer.Registry.AutostartServer = True
  

  SQLServer.Registry.AutostartDTC = True
  

      Note that you cannot make the SQLAgent service autostart on Windows 98, Windows 98 Second Edition, or Windows Millenium Edition (Me) because Windows 9x doesn't have a Service Control Manager like Windows NT does. (SQL Server 2000 no longer supports Windows 95.) Therefore, there is no dependency tracking for Windows 9x as there is for Windows NT 4.0 and Windows 2000.

The Registry and Configuration Objects

      The Registry object exposes many other essential settings, such as installation directory, case sensitivity, path to the master database, and name of registered owner. Not surprisingly, not all of these settings can be modified without reinstalling SQL Server.
      The Configuration object exposes a collection of ConfigValue elements. Each element corresponds to one of the approximately 40 values you can browse and modify with the sp_configure stored procedure, but the object-oriented approach makes dealing with these values simpler. For example, you can print a list of advanced configuration values using the following code:

  Dim cv As SQLDMO.ConfigValue
  

  With SQLServer.Configuration
  

      .ShowAdvancedOptions = True
  

      For Each cv In .ConfigValues
  

          Print cv.Name, cv.RunningValue, cv.CurrentValue
  

      Next
  

  End With
  

      You can change a configuration value and commit your changes with the ReconfigureCurrentValues or ReconfigureWithOverride methods:

  ' allow changes to system tables
  

  With SQLServer.Configuration
  

      .ConfigValues("allow updates").CurrentValue = 1
  

      .ReconfigureWithOverride
  

  End With
  

Extracting Data from the QueryResults Object

      Not all the configuration data can be retrieved so easily. The SQLServer object exposes 10 Enumxxx methods that return configuration information about account info, server attributes, and Windows NT domain groups, as well as crucial dynamic data such as running processes and existing locks. All of these Enumxxx methods return QueryResults objects.
      You can think of a QueryResults object as a container for one or more two-dimensional read-only arrays, from which you extract information using methods such as GetColumnString, GetColumnLong, or GetColumnDouble. Using these methods is quite cumbersome because you must first query the type of each column using the ColumnType property. Only then can you select the appropriate GetColumnxxx method.
      A simpler and faster method of extracting data from the QueryResults object is based on its GetRangeString method, which returns the text that would have been printed in the Query Analyzer window when asking for the same information through the corresponding stored procedure (such as sp_who for listing users and processes, or sp_locks for enumerating existing locks). You can then process this string using the Visual Basic or VBScript Split function to quickly extract all the values you need.
      Figure 3 shows a reusable routine that converts a QueryResults object to an ADO Recordset. I opted for a Recordset instead of, say, a regular two-dimensional string array because I can immediately display the result by binding a DataGrid control to the Recordset (see Figure 4).
      The SQLServer object isn't the only element in the hierarchy that returns a QueryResults object. In fact, I have counted more than 80 Enumxxxx methods in the SQL-DMO object model that let you retrieve almost any conceivable type of information, including dependencies between database entities, replication subscriptions, and tables on linked servers. Of course, you can do more than just display the results to the user. For example, you might implement sophisticated utilities that help you optimize the system by monitoring locks and processes.

The Database Object

      The Database object is probably the most interesting object in the DMO-SQL hierarchy, from a developer's perspective. Once you get a reference to this object, you can enumerate, create, delete, and modify the behavior of all the tables, views, stored procedures, users, and groups of a database, down to the individual column, index, and trigger (see Figure 5).


Figure 5 Database Object

      Because all dependent objects are gathered in collections, enumerating them is quite easy. For example, the code in Figure 6 lists all the tables and their columns in the Pubs database that comes with SQL Server.
      Most database objects expose a SystemObject property that returns True if the object is system-generated and False if it's user-defined, so it's easy to restrict your output to only the objects you're interested in:

  ' list all non-system stored procedures
  

  ' in Pubs, and their T-SQL definition
  

  Dim sp As SQLDMO.StoredProcedure
  

  For Each sp In db.StoredProcedures
  

      If Not sp.SystemObject Then
  

          Print "â€" PROCEDURE " & sp.Name
  

          Print sp.Text
  

      End If
  

  Next
  

Creating and Applying T-SQL Scripts

      Many SQL-DMO objectsâ€"including Database, Table, Index, Key, Job, Alert, Trigger, User, Rule, Check, and most replication objectsâ€"expose the Script method, which returns the T-SQL script that would generate the object. Keep in mind, however, that the script doesn't include inner objects. For example, the following code produces a script that contains only a CREATE DATABASE statement and a bunch of calls to the sp_option stored procedure.

  ' get the T-SQL script for the Pubs database
  

  Dim pubsDB As SQLDMO.Database
  

  Set pubsDB = SQLServer.Databases("pubs")
  

  Print pubsDB.Script
  

To recreate the entire database structure, you must call the Script method for all the inner objects in the Database subhiearchy, including all tables, views, and stored procedures. To help you generate database scripts, though, the table's Script method takes a bit-coded argument that lets you specify whether you also require the scripts for creating indexes, constraints, triggers, and foreign keys, as you can see in Figure 7.
      SQL-DMO can only execute one batch at the time and does not parse SQL scripts for batch separators. Therefore, you cannot run a generated script file back through ExecuteImmediate or any of the other Executexxx methods without parsing the script yourself in Visual Basic. The great thing about programmatically producing T-SQL scripts is that you can process them as strings. For example, you can copy the structure of a table to a table with a different name (in the same or another database) using a simple search-and-replace operation, as shown in Figure 8.

Creating and Scheduling Jobs

      Job management and scheduling is where the SQL-DMO technology really proves its usefulness. In fact, you usually want to tailor the behavior of SQL Server to the needs of specific users, and these needs may change frequently. With the JobServer subtree of the DMO hierarchy (see Figure 9) you can build front-end applications that let your customers create and schedule complex maintenance jobs by using a simplified custom user interface.


Figure 9 JobServer Subtree

      I can't explain every JobServer child object in detail, so I'll focus on the most important and useful ones: the Job object and its dependent JobStep and JobSchedule objects. Using these objects is pretty straightforward if you are familiar with the actions performed in order to both create and schedule a job from within the SQL Server Enterprise Manager.
      Let's see how to set up a job that checks a database for consistency and backs it up if no errors were found. Create a standalone Job object, set its Name and Description properties, then add it to the JobServer's Jobs collection:

     ' create and initialize the Job object
  

     Dim SQLJob As New SQLDMO.Job
  

     SQLJob.Name = "Northwind Backup"
  

     SQLJob.Description = "Check and Backup Northwind"
  

     ' add to the Jobs collection
  

     SQLServer.JobServer.Jobs.Add SQLJob
  

      Each job step in the Enterprise Manager corresponds to a JobStep object in the SQL-DMO hierarchy. There are three distinct types of job steps: T-SQL commands, Windows Script commands, and operating system commands. You decide the type of a job step by assigning a string to its SubSystem property.
      For example, here's how you create a job step that performs a DBCC CHECKDB command on the Northwind database:

     Dim aJobStep As SQLDMO.JobStep
  

     Set aJobStep = New SQLDMO.JobStep
  

     aJobStep.Name = "Step 1: Check Northwind"
  

     aJobStep.StepId = 1
  

     aJobStep.SubSystem = "TSQL"
  

     aJobStep.DatabaseName = "Northwind"
  

     aJobStep.Command = "DBCC CHECKDB ('Northwind', REPAIR_FAST)
  

     aJobStep.OutputFileName = "c:\temp\job1.log"
  

The OutputFileName property specifies the name and path of the file that will receive the output from this job step. The problem with this property is that each job step overwrites any previous contents of the file, unlike what happens when you select an output file in the Enterprise Manager (which conveniently offers an append option). Actually, this is one of the few details in which the SQL-DMO model is seemingly less flexible than the Enterprise Manager. You can work around this limitation by having each step of a multistep job send its output to a different temporary file, then you can concatenate their contents into the actual output file.
      You decide what happens when the job step completes successfully or terminates with an error by assigning a value to the OnSuccessAction and OnFailureAction properties, respectively. These properties take one of the following four enumerated values: SQLDMOJobStepAction_QuitWithSuccess, SQLDMOJobStepAction_QuitWithFailure, SQLDMOJobStepAction_GotoNextStep, or SQLDMOJobStepAction_GotoStep.
      For example, you specify that you jump to the next step (that is, step 2) if the DBCC CHECKDB command completes successfully, whereas you terminate the job if the command fails:

  aJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
  

  aJobStep.OnFailureAction = SQLDMOJobStepAction_QuitWithFailure
  

      You can also jump to any other step in the job, in which case you must assign the StepId of the target job step to either the OnSuccessStep or the OnFailureStep properties:

  ' if DBCC CHECKDB fails, jump to 3rd step
  

  aJobStep.OnFailureStep = 3
  

  aJobStep.OnFailureAction = SQLDMOJobStepAction_GotoStep
  

      When you've completed the definition of the current job step, you add the JobStep object to the parent Job's JobSteps collection:

  SQLJob.JobSteps.Jobs aJobStep
  

      Now you can define additional job steps. For example, in the second step of the code in Figure 10, you back up the database if the DBCC CHECKDB command was successful, and in the third step you run an ActiveX® script that gathers the output from the individual job steps and generates a log file for the entire application. To run an ActiveX script program you set the JobStep's SubSystem property to the value "ActiveScripting", the DatabaseName to the name of the script language (VBScript or JScript), and the Command property to the script code. (You can run an operating system command by setting the SubSystem property to the "CmdExec" string, and the Command property to the operating system command to run, such as IISRESET START to start Internet-related services.)
      Once you have created a Job object, you apply it to a given SQL Server object and run it with either the Invoke or the Start method. These methods are equivalent, except that the Start method lets you specify the starting job step.
      To complete your task, you should prepare one or more schedules for the Job you've just created. You do so by creating a standalone JobSchedule object, set its properties, and add it to the JobSchedules collection of the Job object. You assign start time, end time, and frequency of the job schedule through the JobSchedule's Schedule object, as shown in Figure 11. You can define a daily, weekly, or monthly schedule, and indicate on precisely which weekdays the job will be executed. Note that the Schedule object's ActiveStartDate and ActiveEndDate properties are Long values in the format yyyymmdd, and the ActiveStartTime and ActiveEndTime properties are Long values in the format hhmmss.

Conclusion

      The companion code for this article includes a demo application that lets the user select one or more databases and back them up immediately, or schedule the operation every n days, starting with a given date and time (see Figure 12). When you click on the OK button, the program dynamically creates a multistep Job object and initializes its child JobSchedule object according to the user's preferences. This is an example of a simple but secure user interface that most users will appreciate.


Figure 12 Scheduling Database Backups

      In this article I have merely scratched the surface of the SQL-DMO object model, but it should serve as a good introduction. Most objects in the DMO hierarchy work in a rather predictable way, so the learning curve is smooth. Becoming familiar with the many objects in this object model takes some time, but you will immediately be productive if you start with the information and examples I've provided here.