SQL Distributed Management Objects (SQL-DMO) is a set of COM objects that enables the programming of administrative tasks for Microsoft SQL Server. Because SQL-DMO is a collection of COM objects, you can program
Installing and Using SQL-DMO
Since I am an Access developer, this article shows how to program SQL Server with Access 2000. However, the general principles apply to VBA applications in other applications and stand-alone VB applications as well. With Access 2000, you can program SQL-DMO from either classic .mdb files or the new .adp files. However, you must have the DLL implementing SQL-DMO on your computer. This DLL has the name sqldmo.dll for SQL Server 7, MSDE, and SQL Server 2000. Access developers can install this file by installing the MSDE that ships with Office 2000. Others can obtain it by installing either SQL Server 7 or SQL Server 2000. You do not need a server installation--just the Client Management Tools are sufficient. Once the DLL is on a workstation, you can program a remote server provided your login has appropriate permissions.
Access developers using the MSDE will notice that Help is not available for SQL-DMO. This is because MSDE ships without the SQL-DMO Help file. This file is sqldmo.hlp for SQL Server 7 and sqldmo80.hlp for SQL Server 2000. If you have a spare client access license, you can obtain either help file by installing the Client Management Tools. If not, there are many code samples and other useful information for SQL-DMO in the free copy of Books Online for SQL Server 7 that you can download from this page.
You may have noticed from the preceding paragraph that the Help files for SQL Server 7 and SQL Server 2000 are not identical. This is because SQL-DMO for SQL Server 2000 is a superset of SQL-DMO for SQL Server 7. The SQL-DMO DLL has the same name on both servers, but the SQL Server 2000 version includes objects not available in the SQL Server 7 version. You can create references and build SQL-DMO apps on SQL Server 7 computers, and then run them from another computer with SQL Server 2000 installed. The reverse is not true.
Logging On and Working with Collections
Before attempting to run any of the sample code in this article, create a reference to the SQL-DMO object library. You can do this in the VBA project associated with an Access database file or Access project file by choosing Tools > References. Then, select Microsoft SQLDMO Object Library and click OK to close the References dialog.
The first listing includes two procedures (see below). These procedures demonstrate how to log into a SQL Server and work with one of the collections on the server. The first procedure passes the server name, login name, and password as strings to the second procedure. This second procedure logs into the server with the passed login and password. The code sample represents a server with a pointer named srv1 declared as a SQLDMO.Sever object. The object's Connect method logs into the server. Next, the procedure prints the number of databases on the server by referencing the Count property of the server's Databases collection. Before closing, the procedure disconnects from the server and sets the pointer to Nothing.
Enumerating the Members of a Collection
The second listing shows how to expand the capabilities of the preceding sample by enumerating the members of a collection. In this example, it prints the database name and size for each database on a server. The overall design is identical to the first sample, except for the replacement of the statement printing the number of databases with a For Each...Next statement to loop through the databases on a server. It prints the Name and Size properties of each Database object within the Databases collection on a server.
Within any given database, there are other collections such as tables, views, and stored procedures. You can adapt the logic demonstrated in the second listing to enumerate the members of these collections. Reference any individual item in a collection by following its collection name with an identifier in parentheses. For example, the following expression walks the SQL-DMO hierarchical model to return the name of the first table in the Northwind database on a SQL Server:
SQLDMO.SQLServers(srvname).Databases("Northwind").Tables(1).Name Enumerating NameList Objects
Selected SQL-DMO methods return lists of components by name instead of as a set of individual objects. A method returning component names instead of objects returns a NameList object. You cannot loop through NameList elements with a For Each...Next loop. Instead, you must use a For...Next statement that passes through the elements in the NameList object. Dump the return names into a SQL-DMO Names collection object in order to execute the loop for individual names. The ListAvailableSQLServers for the SQL-DMO Application object returns a NameList object with the names of all the network-visible SQL Server installations that a SQL Server can see. While the return value from the method can vary slightly for SQL Server 7, MSDE, and SQL Server 2000, these installations will always include any explicitly listed server in the Client Network Utility dialog. With MSDE, it is essential for you to include servers in the Client Network Utility dialog if you want the ListAvailableSQLServers method to return them.
The third listing demonstrates the syntax for returning the list of available servers from a workstation. This method returns a NameList object that the avs1 pointer references. Since the return set from the ListAvailableSQLServers method is not a collection, you cannot generally use a For Each...Next loop to pass through its members. Instead, you must use a For...Next statement with an index that points to individual names in the Names collection object. The index for the names is one-based.
This article introduced you to SQL-DMO programming. You can use its samples to formulate your own SQL-DMO solutions. First, you learned how to connect to a server. Second, you discovered how to work with SQL-DMO collections--both with the Count property and the For Each...Next statement. Third, the article presented a sample demonstrating how to work with NameList objects. These objects do not appear in typical VBA and VB application development, but there are several uses for them with SQL-DMO.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best MS SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our SQL Server gurus are waiting to answer your toughest questions.
Listing 1. Logging into a server and referencing the Databases collection.
Sub call_count_databases() 'Pass along server name, login, and password 'to routine to count databases on a server count_databases "cabarmada", "sa", "" End Sub Sub count_databases(srvname As String, _ login As String, password As String) Dim srv1 As SQLDMO.SQLServer 'Connect to a SQL Server Set srv1 = New SQLDMO.SQLServer srv1.Connect srvname, login, password 'Count of databases on connected server Debug.Print "Server " & srvname & " has " & _ srv1.Databases.Count & " databases on it." 'Cleanup routine srv1.Disconnect Set srv1 = Nothing End Sub
Listing 2. Enumerating the members of the Databases collection on a server.
Sub call_enumerate_databases() 'Pass along server name, login, and password 'to routine to enumerate databases on a server enumerate_databases "cabarmada", "sa", "" End Sub Sub enumerate_databases(srvname As String, _ login As String, password As String) Dim srv1 As SQLDMO.SQLServer Dim dbs As SQLDMO.Database 'Connect to a SQL Server Set srv1 = New SQLDMO.SQLServer srv1.Connect srvname, login, password 'Enumerate the databases on connected server For Each dbs In SQLDMO.SQLServers(srvname).Databases Debug.Print dbs.Name & " uses " & dbs.Size & _ "MB of storage." Next 'Cleanup routine srv1.Disconnect Set srv1 = Nothing End Sub
Listing 3. Listing the names in a NameList object.
Sub print_available_servers() Dim avs1 As SQLDMO.Names Dim cnt1 As Integer 'Return list of available servers in a Names collection 'and count of servers in an Integer variable Set avs1 = SQLDMO.Application.ListAvailableSQLServers cnt1 = SQLDMO.Application.ListAvailableSQLServers.Count 'Enumerate servers in the Namelist variable 'through the avs1 Names collection object For i = 1 To cnt1 Debug.Print avs1(i) Next i End Sub
About the Author
Rick Dobson, Ph.D., and his wife, Virginia, operate a development and training consulting practice that specializes in SQL Server, Access, Office, and web development. Rick is the author of two best-selling books: "Programming Microsoft Access 2000" (Microsoft Press) and "Professional SQL Server Development with Access 2000" (Wrox Press). Rick and Virginia also jointly host national seminar tours on Access and SQL Server development. You can reach Rick or Virginia at either ProgrammingMSAccess.com or CAB, Inc.
For More Information
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums. Also, give us your feedback about this tip in the "Sound Off" forum.
- Check out our new Ask the Experts feature: Our SQL Server guru is waiting to answer your toughest questions.
This was first published in February 2005