Managing SQL Server with VB

Unleash the power and flexibility of SQL Server's DMO functions with SQL Database Monitor

VB Solutions is about using Visual Basic to build a variety of solutions tospecific business problems. This column doesn't teach you how to write VB, buthow to use VB as a tool to provide quick, easy-to-implement solutions that youcan use right away. Last month, I presented a network administration utilitythat used some Win32 APIs to quickly check for NT security violations. Thismonth, I'll show you how SQL Server's Distributed Management Objects (DMO) letyou use VB to create custom management tools for SQL Server.

Because you can control SQL Server from VB, you can easily develop customSQL Server management functions. And if you use DMO from VB, you can easilybuild tools that streamline some functions that SQL Server's graphicalAdministration tool provides. For instance, viewing the storage requirements forall the tables in a SQL Server database requires you to traverse several SQLEnterprise Manager screens or write SQL programs in SQL Server's Transact SQLlanguage, which has limited graphic elements. SQL Server's DMO opens up SQLServer to a custom VB program that can consolidate SQL Server information andpresent it through VB's built-in graphical controls.

Screen 1 previews this month's utility, the SQL Database Monitor. The SQL Database Monitor helps you quickly see the size of all the tables in a SQLServer database by presenting the tables and their respective sizes in a graph.In addition to table size, Database Monitor offers a quick view of the number ofrows in each table and other information such as the table creation date.

To use the SQL Database Monitor, you first must select the name of the SQLServer you want to connect to and then click Connect. The SQL Database Monitorwill present a login prompt for your SQL Server login ID and password. After yousuccessfully log on to SQL Server, the SQL Database Monitor lists all thedatabases you have access rights to. Double-clicking a database name causes theSQL Database Monitor to graph all the user-created tables in the selecteddatabase. The tabbed dialog at the bottom of the screen lets you choose from agraph showing the size in KB of each table, a graph showing the number of rowsfor each table, and a list containing more detailed table information.

To get programmatic access to this kind of database information fromnon-SQL Server platforms (if it is available at all), you need to masterlow-level networking and system interfaces. Luckily, SQL Server is built on anObject Linking and Embedding (OLE) foundation, DMO, that makes access to SQLServer's database management functions easy. For the background we'll need tounderstand the detailed workings of the SQL Database Monitor, let's get a quickoverview of SQL Server's DMO.

Overview of DMO
DMO is a set of 32-bit OLE objects that enable program access to SQLServer's management functions. Microsoft introduced SQL Server's DMO in SQLServer 6.0 and enhanced it in SQL Server 6.5. Microsoft intended SQL Server'sDistributed Management Framework to facilitate the use of SQL Server in adistributed environment. The Distributed Management Framework extends the powerof SQL Server's management function to all the clients in the network. BecauseSQL Server implements the DMO as OLE objects, you can use them only from a32-bit client application. Although this month's example SQL Database Monitorprogram is written in VB, SQL Server's DMO functions are not limited to VB. Any32-bit OLE-compliant application, including VB, Visual C++, and Delphi, can usethem.

DMO is a part of the larger Distributed Management Framework that Microsoftprovides for SQL Server. Figure 1 shows you the three-tiered architecture of SQLServer's Distributed Management Framework. The SQL Server Engine and the SQLExecutive functions make up the foundation, providing the core database andmanagement services of SQL Server. The DMO is the middle level and bridges theuser interface and the core SQL Server components. SQL Server's SQL EnterpriseManager and the OLE interface that is open to your VB applications use the DMO.With more than 60 different objects and 1000 properties and methods, SQLServer's DMO gives you far-reaching access to SQL Server's capabilities.

Microsoft organized the DMO into the hierarchical order shown in Figure 2.The application object is at the top of the DMO organization. The SQL Serverobjects are under the application object. Each SQL Server object contains one ormore database objects, and each database object contains the DMO table objectsand other types of SQL Server objects such as triggers, views, and storedprocedures. Each level of the DMO hierarchy consists of objects that relate tothe level. For instance, the SQL Server level consists of device, login, anddatabase objects, and the table level includes column and trigger objects. Thishierarchy follows the same basic organization of SQL Server's EnterpriseManager.

Adding DMO Support to VB
Before you can use the DMO in VB's development environment, you must installit. SQL Server copies the files that provide the basic support for DMO to yourclient system when you first install the SQL Server 32-bit client. However, youneed to install the files in VB's development environment to use them from VB.To add DMO support to VB, you need to reference the SQL DMO type library fromVB. Select References from VB's Tools menu to display the References dialog yousee in Screen 2.

Scroll through the References dialog until you see the Microsoft SQLOLEObject Library. Check this list entry to add the sqlole32.tlb file to VB's IDE.(The SQL Server DMO functions are in the sqlole32.dll.) When you add OLE customcontrols (OCXs) and ActiveX controls to VB, you see an icon representing thecontrol in VB's toolbox. Unlike ActiveX controls or OCXs, SQL Server does notadd visual objects to VB's IDE, and you will not see an additional icon in VB'stoolbox. To work with the SQL OLE object, you must use VB's Object Browser tosee the added SQL DMO functions.

You're now ready to implement DMO. See "Using SQL Server's DMO,"for the steps you need to follow.

Inside the SQL Database Monitor Utility
Now that you have a basic understanding of how to use SQL Server's DMO, youcan build this month's utility. In the VB program, you must create the SQLServer DMO OLE object. Because you must access this OLE object from severaldifferent VB forms, I created the object as a public variable in the sqlole.basfile with the following code:

After you create a SQL DMO object, you can use it to connect to SQL Server.In the SQL Database Monitor application, select a SQL Server name, and clickConnect. In addition to the SQL Server name, you need to provide a login ID anda password to connect to SQL Server. To collect this information, the SQLDatabase Monitor displays a simple login dialog that lets the user enter thelogin ID and password values. After obtaining the login information, the SQLDatabase Monitor uses the DMO Connect function shown in Listing 1 to connect toSQL Server.

You can see the Connect method at callout A in Listing 1. The SQL DMOConnect method takes three parameters: the server name, the login ID, and thepassword. Again, the server ID comes from the SQL Server drop-down list shown inScreen 1, and the login ID and password come from the SQL Database Monitor loginwindow.

After the Connect method executes, always test for a successful connection.In Listing 1, the Err.Number property contains any Error codes the SQL-DMOConnect method generates. If an error occurs during Connect method execution, amessage box displays to let you retry the connection or cancel the currentconnection attempt. If you cancel it, you go back to the main window of the SQLDatabase Monitor. This option lets you select a different SQL Server or providea new user login and password. Select retry to have the SQL Database Monitorrerun the Connect method with no changes to the login information.

After the Connect method has successfully completed, you can use the otherSQL DMO functions. SQL Database Monitor lists the databases that are on theconnected SQL Server. The code at callout B in Listing 1 shows how to use SQLDMO to get a list of the SQL Server databases. The Databases property inoSQLServer contains a collection of the database names for the SQL Server. Atcallout B, the code uses VB's For Each operation to loop through thecollection of database names and add each name to the list of databasesdisplayed in SQL Database Monitor's main window.

After the Database Monitor lists all the SQL Server databases, you can pickthe SQL Server database to monitor. Double-click one of the database names.Database Monitor retrieves a list of tables for that database and then graphsthe table sizes and information in the tabbed dialog shown at the bottom ofScreen 1. The code that retrieves the database table information is shown inListing 2.

The code shown at callout A puts the selected database name from the listentry into the sDBName string. The code shown at callout B uses the stringcontaining the database name to extract the collection of table names from theoSQLSever object. Again, the VB For Each operation loops through thecollection of SQL Server table names. Because each database contains both systemand user tables, the SQL Database Monitor checks the TypeOf property todetermine whether the table is a user table or a system table. If it is a usertable, the SQL Database Monitor adds the table name to both graphs and the listsin the tabbed dialogs at the bottom of SQL Database Monitor's main window.Database Monitor adds the table name and amount of disk space used toGraph_Size; the table name and the number of rows to Graph_Rows; and the tablename, amount of disk space used, number of rows, and the table creation date tothe list of tables.

You can now quickly see a graphical view of the table sizes in a SQL Serverdatabase. The tabbed dialog lets you switch among the different database graphs.

Power and Flexibility
I hope this look inside the SQL Database Monitor utility gives you an ideaof the power and flexibility of SQL Server's DMO functions. The code examplesshow how DMO opens up the power of SQL Server's management functions to VB.DMO's OLE implementation makes it easy to use from VB and other OLE-compliantapplications. For more information, refer to Microsoft's Programming SQLDistributed Objects manual and the Microsoft Developer NetworkCD-ROM, which contains several articles and programming examples of how to useDMO.

We Want Your VB Code!
Windows NT Magazine wants to publish your VB solutions. Sendus any interesting and useful VB solutions you've created for your business'sproblems. If we agree that your VB solutions are valuable to our readers, we'llpublish your code and pay you $100. You can send contributions or ideas for VBsolutions to me at [email protected].

Obtaining the Code
The complete sourceand the executable code for this VB Solution is available for downloading from Windows NT Magazine's Web site at www.winntmag.com.