SQL-DMO: Learning the Basics

The SQL Distributed Management Objects (SQL-DMO) model exposes objects, methods, properties, and events through a COM interface to control SQL Server administrative tasks. SQL-DMO has a COM interface, so Visual Basic for Applications (VBA), Visual Basic (VB), and C++ developers can readily use SQL-DMO to build administrative tasks into their custom applications. This article is the first in a series about SQL-DMO programming. The series' objective is to give Microsoft Access and other developers a good grasp of SQL-DMO so that they can program custom solutions that contain administrative functions for SQL Server.

SQL-DMO is particularly appropriate for developers who work with the Microsoft SQL Server Desktop Engine (MSDE) that ships with Access 2000. This database server is a scaled-back version of SQL Server 7.0. Although MSDE provides less functionality than SQL Server 7.0, MSDE ships without charge as part of Microsoft Office 2000. However, the MSDE version that ships with Office 2000 doesn't include Enterprise Manager. So, MSDE users don't have the benefit of a GUI with which to manage database objects. (For more information about differences between MSDE and SQL Server, see "What Is MSDE?" February 2000.) In this article, you'll see some approaches to enumerating SQL Server objects with SQL-DMO. You'll also learn how to empower users to explore SQL Server tables and their columns from an Access form.

Installing the MSDE that ships with Office 2000 automatically installs sqldmo.dll, which implements SQL-DMO for use with SQL Server 7.0 and MSDE. However, Office 2000 doesn't include the SQL-DMO Help file, sqldmo.hlp. Fortunately, developers working with MSDE have other alternatives. For one, if you have a spare client license for SQL Server 7.0, you can install the client-management tools; this installation process also implements sqldmo.hlp. Another alternative, SQL Server 7.0 Books Online (BOL), offers many Help pages on SQL-DMO. Also, a couple of articles can help you understand SQL-DMO programming better: Jason Fisher, "SQL-DMO Picks Up Where Enterprise Manager Leaves Off," February 2001, and my article "Programming SQL-DMO from Access," July 2000, which introduces SQL-DMO for Access developers.

Enumerating Database Specifications
Before you run the listings in this article, you must start an Access 2000 project that references the SQL-DMO library. You can use the SQL Server Database Wizard to create a new SQL Server database for your project. Then, in the Database window, select Objects, Modules in the Objects, and click New to open an empty VBA project. From the Visual Basic Editor window, choose Tools, References, Microsoft SQLDMO Object Library. Then, close the References dialog box. Your VBA project is now ready to run the listings in this article.

Listing 1, page 25, shows a basic SQL-DMO program that prints specifications for all the databases on a server. The code creates a list that correlates each database with its creation date, as well as with the number of tables, views, and stored procedures within the database. After declaring a SQLServer object from the SQL-DMO library, the program instantiates the object, then connects it to the cabxli server with a systems administrator (sa) user ID (UID) and a blank password. (Of course, you need to change the server name, UID, and password so that they are appropriate for your needs.) Generally when you develop with SQL-DMO, you need a UID with broad server permissions because you use SQL-DMO to program administrative functions. (For more information about security, see my columns "Access Granted," December 2000, "Securing SQL Server Tables," January 2001, "Homegrown Security Solutions," February 2001, and "Programming SQL Server Security," March 2001.)

Next, the code in Listing 1 scans a server's databases to calculate a value that's 1 greater than the number of characters in the longest database name. The code uses an error trap to determine this value by estimating the length of the longest database name. The error trap extends the current estimate until no name is larger than the estimate. The application can align the information for all rows by knowing the longest database name, even when the database names on different rows are of substantially different lengths. Before using this maximum length for database names, the application prints a column-header row to describe the database rows. This line's expression confirms how to use the maximum length to distribute values across the Immediate window through the Debug object's Print method. Choose View, Immediate Windows to see the Immediate window.

The code's final loop looks through the databases again to gather the print information for each row. The Do loop reuses the Databases collection expression saved in obj1 to enhance performance. Within the loop, the syntax for the Print method's argument shows that the Databases collection has other collections that are hierarchically dependent on it. These collections determine how the code in Listing 1 computes items such as the number of tables.

Using SQLObjectList Objects
VBA and VB developers generally have substantial experience working with the members of hierarchically related collections. However, SQL-DMO lets you map the members of many collections by using a basic element called a SQLObjectList object. This object shows the names of SQL Server component members without exposing the collection's actual members. Working with SQLObjectList objects is preferable to working with the collection's members when you need only a simple enumeration of SQL Server database object names. If you need to manipulate the members or need information other than names, the SQLObjectList object isn't appropriate.