Ken Lassesen
Microsoft Developer Network Technology Group
October 11, 1995
Click here for a diagram of the SQL Distributed Management Object in various graphics formats.
This article consists primarily of a graphic map of the SQL Distributed Management Object (SQL-DMO), showing its properties, methods, and child objects. The SQL-DMO may be accessed using Visual Basic®, Access Basic, Visual Basic for Applications, or Visual C++®.
The SQL Distributed Management Object (SQL-DMO) enables the developer to fully control Microsoft® SQL Server version 6.0 or higher. In most prior versions of SQL Server, the server could be administered by programs that are shipped with SQL Server, such as ISQL.EXE, ISQLW.EXE, Winquery.EXE, or pass-through queries using the Data Access Object (DAO). Windows® SQL Server version 4.21 introduced a very well-received graphic interface for administration. The introduction of an OLE Automation server (SQLOLE) simplifies administration further and allows easy programmatic administration. This approach presents the developer with a programming object instead of a massive (and confusing) collection of stored procedures.
The SQLOLE object is available from Visual C++® version 4.0 and many products that contain Visual Basic® for Applications, including Microsoft Excel version 5.0 and higher, Microsoft Access 95, and Visual Basic version 4.0. The SQL-DMO may be used from other products that support references, for example, Microsoft Project. For further information on using the SQLOLE, see the Microsoft SQL Server product documentation (Development Library, Product Documentation, SDKs) and search the Development Library CD. For an introduction, see my article, "Using Microsoft OLE Automation Servers to Develop Solutions."
Figure 1 shows the relationship between objects as described by the Object Viewer and online Help files. A map is a good learning aid and a quick reference when developing an application. After producing a map, I found that it was easy to work with the object: I just post the appropriate map on my wall for quick reference, which is a lot faster than clicking objects in a Help file one by one to discover their properties and methods.
Because Figure 1 is unreadable online, I have included the two most common graphics formats—encapsulated PostScript™ (.EPS) and Windows metafile (.WMF)—as well as a copy of my original Shapeware® Visio™ version 4.0 file (.VSD). The first two formats can be printed across multiple pages using any of the commercial graphics applications—such as Adobe™ PageMaker™, Corel® Draw, or Microsoft Publisher—or using Microsoft Excel. The original Visio file is included for those who have a copy of Visio 4.0 and wish to modify the diagram easily.
Figure 1. The SQL Distributed Management Object
The objects and collections in Figure 1 are defined in Table 1. The objects are listed in the same sequence as they appear in the map.
Table 1. Object and Collection Definitions
| Object or Collection | Definition |
| Application object | This object is a standard OLE object that provides common properties and methods for managing the application environment. Each SQL-DMO application has a single Application object. |
| SQLServers collection | This collection contains all SQL Server objects of the Application object. |
| SQLServer object | This object is the top-level object for the SQL Server OLE object model. All other SQL-DMO objects are either contained within or accessed through a SQL Server object or one of its children. |
| Properties collection | This collection contains all the Property objects for a specific instance of an object. It is read-only. |
| Property object | This object is a built-in characteristic or a user-defined characteristic of a data access object. The Property object and the Properties collections are only available to Visual Basic programs and other OLE Automation controllers. They are not available to C++ programs. |
| QueryResults object | This object contains the result set from an executed Transact-SQL query. The query results returned from SQL Server are stored in memory and cannot be changed. |
| Alerts collection | This collection contains all Alert objects. |
| Alert object | This object contains all the information that defines a SQL Server alert. When you use it with Alerts collection, you have the ability to set alerts on SQL Server events. For example, you can generate alerts when a database is about to become full or when a database dump is unsuccessful. The engine generating the alerts is described in the AlertSystem object below. |
| Operators collection | This collection contains Operator objects. |
| Operator object | This object contains all the information about a SQL Server operator.. |
| Languages collection | This collection contains all Language objects. |
| Language object | This object contains all the information about each language supported by SQL Server. |
| Devices collection | This collection contains all Device objects used by a SQLServer object. |
| Device object | This object describes a pre-allocated physical storage used by SQL Server for storing database information. This includes database devices and dump devices. |
| Registry object | This collection contains all of the information stored in the Windows NT™ Registry for a SQL Server. |
| Logins collection | This collection contains all Login objects for a SQL Server. |
| Login object | This object consists of a log-in identifier and password used to connect to SQL Server. A Login object is global to the entire SQL Server and has corresponding User or Alias objects in each database for which the Login object has access. |
| Executive object | This collection contains information about the scheduling engine used to schedule tasks and alerts. |
| AlertSystem object | This object contains information about the alert engine. See Alert collection above. |
| Tasks collection | This collection contains all of the scheduled tasks for a SQL Server. |
| Task objects | These objects contain information about a scheduled task.. |
| IntegratedSecurity object | This collection contains all the information about how SQL Server security and log-ins are integrated with Windows NT group and user accounts. |
| Configuration collection | This collection contains a collection of ConfigValue collections. |
| ConfigValues collection | This collection contains the collection of information on how the SQL Server is configured. |
| ConfigValue object | This object contains information on each SQL Server configuration option. |
| Names collection | This collection contains strings instead of objects. |
| RemoteServers collection | This collection contains all of the RemoteServer objects for a specific SQL Server. |
| RemoteServer object | This object contains information about a remote SQL Server that is known to the local SQL Server. |
| RemoteLogins collection | This collection contains all the RemoteLogin objects for a specific SQL Server. |
| RemoteLogin object | This object contains information about the association of a remote log-in from a specific remote server to a local log-in. Each RemoteServer object can have multiple RemoteLogin objects. |
| SubscriberInfo object | This object contains all the replication subscribing information about a RemoteServer object. |
| Databases collection | This collection contains information about all of the databases on a SQL Server. |
| Database object | This object contains other objects (like Table, View, and StoredProcedure objects) which contain or interact with data. |
| SystemDataTypes collection | This collection contains all of the SystemDataType objects. |
| SystemDataType object | This object contains information about a system-supplied data type. |
| DBOption object | This object contains all the database options for a database. These options can be set by the database owner. |
| Defaults collection | This collection contains the information about all of the bindable (non-DRI) default values for a database object. |
| Default object | This object is a value that is stored in a column or data type to which it is bound if no other value is supplied. |
| Groups collection | This collection contains the information about all of the groups for a database object. |
| Group object | This object contains a group of users and can be used to grant privileges to a large number of users with similar access rights. |
| DBObject object | This object is a generic object that contains information appropriate for all types of database objects. A DBObject object is closely tied to a specific SQL-DMO object, but presents a generic interface that allows the same code to apply to multiple object types. A DBObject object can represent a Table, View, Rule, Default, StoredProcedure, Trigger, or UserDefinedDatatype object. |
| Rules collections | This collection contains the rules that apply to a database. |
| Rule object | This object contains information that defines a domain of valid values that can be stored in the column or data type to which it is bound. |
| StoredProcedures collection | This collection contains the stored procedures that apply to a database. |
| StoredProcedure object | This object is a named set of Transact-SQL statements that are stored within a database. |
| Tables collection | This collection contains all of the tables in the database. |
| Table object | This object contains rows of data in the database. |
| Keys collection | This collection contains all of the key objects (DRI constraints) associated with a table. |
| Key object | This object contains information about primary, unique, or foreign keys of a Table object. Each Key object is made up of one or more columns from a table. |
| Indexes collection | This collection contains all of the indexes associated with a table. |
| Index object. | This object contains information about an index. The index may be clustered or nonclustered. |
| Columns collection | This collection contains all the column objects for a table. |
| Column object | This object defines a specific field of information that is maintained in a table. |
| DRIDefault object | This object contains information about a default constraint for a column. |
| Checks collection | This collection contains all of the check constraints for a table. |
| Check object | This object contains information about a check constraint. |
| Trigger collection | This collection contains all of the triggers existing for a table. |
| Trigger object | This object contains Transact-SQL statements that are executed when data in a specific table is inserted, updated, or deleted. |
| Views collection | This collection contains all of the views in a database. |
| View object | This object is defined by a SELECT statement (the Text property) that includes specific columns (either actual or aggregated) from one or more tables in a database. |
| Publications collection | This collection contains all of the publications for a database. |
| Publication object | This object contains information about a publication containing articles used for replication. |
| Articles collection | This collection contains all of the articles in a publication. |
| Article object | This object contains information about an article contained in a publication. |
| Subscriptions collection | This collection contains all of the subscriptions for an article. |
| Subscription object | This object contains information about a subscriber to a replicated article. |
| Permission object | This object contains information about a database user's object or statement privileges. It is returned by various methods. |
| Backup object | This object contains complete information used to perform a backup or restore of a database or transaction log. |
| HistoryFilter object | This object is used to qualify task history rows to list or purge. |
| TransactionLog object | This object contains all the information about a database's transaction log. |
| UserDefinedDatatypes collection | This collection contains all of the user-defined data types for a database. |
| UserDefinedDatatype object | This object contains information about a new data type that has been created by the user. |
Programming SQL Distributed Management Objects. (MSDN Library, Platform, SDK, and DDK Documentation)