Managing SQL Server with VB

Downloads
vbcodedec.zip

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

Because you can control SQL Server from VB, you can easily develop custom SQL Server management functions. And if you use DMO from VB, you can easily build tools that streamline some functions that SQL Server's graphical Administration tool provides. For instance, viewing the storage requirements for all the tables in a SQL Server database requires you to traverse several SQL Enterprise Manager screens or write SQL programs in SQL Server's Transact SQL language, which has limited graphic elements. SQL Server's DMO opens up SQL Server to a custom VB program that can consolidate SQL Server information and present 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 SQL Server 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 of rows 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 SQL Server you want to connect to and then click Connect. The SQL Database Monitor will present a login prompt for your SQL Server login ID and password. After you successfully log on to SQL Server, the SQL Database Monitor lists all the databases you have access rights to. Double-clicking a database name causes the SQL Database Monitor to graph all the user-created tables in the selected database. The tabbed dialog at the bottom of the screen lets you choose from a graph showing the size in KB of each table, a graph showing the number of rows for each table, and a list containing more detailed table information.

To get programmatic access to this kind of database information from non-SQL Server platforms (if it is available at all), you need to master low-level networking and system interfaces. Luckily, SQL Server is built on an Object Linking and Embedding (OLE) foundation, DMO, that makes access to SQL Server's database management functions easy. For the background we'll need to understand the detailed workings of the SQL Database Monitor, let's get a quick overview of SQL Server's DMO.

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

DMO is a part of the larger Distributed Management Framework that Microsoft provides for SQL Server. Figure 1 shows you the three-tiered architecture of SQL Server's Distributed Management Framework. The SQL Server Engine and the SQL Executive functions make up the foundation, providing the core database and management services of SQL Server. The DMO is the middle level and bridges the user interface and the core SQL Server components. SQL Server's SQL Enterprise Manager 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, SQL Server'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 Server objects are under the application object. Each SQL Server object contains one or more database objects, and each database object contains the DMO table objects and other types of SQL Server objects such as triggers, views, and stored procedures. Each level of the DMO hierarchy consists of objects that relate to the level. For instance, the SQL Server level consists of device, login, and database objects, and the table level includes column and trigger objects. This hierarchy follows the same basic organization of SQL Server's Enterprise Manager.

Adding DMO Support to VB
Before you can use the DMO in VB's development environment, you must install it. SQL Server copies the files that provide the basic support for DMO to your client system when you first install the SQL Server 32-bit client. However, you need 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 from VB. Select References from VB's Tools menu to display the References dialog you see in Screen 2.

Scroll through the References dialog until you see the Microsoft SQLOLE Object 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 custom controls (OCXs) and ActiveX controls to VB, you see an icon representing the control in VB's toolbox. Unlike ActiveX controls or OCXs, SQL Server does not add visual objects to VB's IDE, and you will not see an additional icon in VB's toolbox. To work with the SQL OLE object, you must use VB's Object Browser to see 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, you can build this month's utility. In the VB program, you must create the SQL Server DMO OLE object. Because you must access this OLE object from several different VB forms, I created the object as a public variable in the sqlole.bas file with the following code:

Public oSQLServer As New

SQLOLE.SQLServer

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 click Connect. In addition to the SQL Server name, you need to provide a login ID and a password to connect to SQL Server. To collect this information, the SQL Database Monitor displays a simple login dialog that lets the user enter the login ID and password values. After obtaining the login information, the SQL Database Monitor uses the DMO Connect function shown in Listing 1 to connect to SQL Server.

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

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

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

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

The code shown at callout A puts the selected database name from the list entry into the sDBName string. The code shown at callout B uses the string containing the database name to extract the collection of table names from the oSQLSever object. Again, the VB For Each operation loops through the collection of SQL Server table names. Because each database contains both system and user tables, the SQL Database Monitor checks the TypeOf property to determine whether the table is a user table or a system table. If it is a user table, the SQL Database Monitor adds the table name to both graphs and the lists in 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 to Graph_Size; the table name and the number of rows to Graph_Rows; and the table name, amount of disk space used, number of rows, and the table creation date to the list of tables.

You can now quickly see a graphical view of the table sizes in a SQL Server database. 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 idea of the power and flexibility of SQL Server's DMO functions. The code examples show 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-compliant applications. For more information, refer to Microsoft's Programming SQL Distributed Objects manual and the Microsoft Developer Network CD-ROM, which contains several articles and programming examples of how to use DMO.

We Want Your VB Code!
Windows NT Magazine wants to publish your VB solutions. Send us any interesting and useful VB solutions you've created for your business's problems. If we agree that your VB solutions are valuable to our readers, we'll publish your code and pay you $100. You can send contributions or ideas for VB solutions 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.