| Downloads |
|---|
| ntvbse.zip |
\[Editor's Note: 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.
By using SQL Server's Distributed Management Objects (DMO) from Visual Basic (VB), you can easily build custom applications that access SQL Server's functionality. Last month, I showed how to use DMO to create a custom management tool for SQL Server. This month, I'll build on that foundation by showing you how to use DMO to access the data in SQL Server databases.
Preview of the Graphical Data Export Tool
Screen 1 previews this month's utility, the SQL Export Utility. The SQL
Export Utility is a graphical data export tool that lets you export data from a
SQL Server table to a flat file. Exporting SQL Server data is important when you
need to transfer SQL Server tables and views to other applications and platforms
that can't access SQL Server directly. For instance, if you need to transfer SQL
Server data to a mainframe or an AS/400, you must first export the SQL Server
data and then run a file transfer program to upload the data to the host. SQL
Server provides a command-line bulk copy program that's easy to operate, but it
doesn't have a graphical interface. This month's SQL Export Utility combines VB
4.0's TreeView control with SQL Server 6.5's DMO BulkCopy object to provide a
simple file export utility with a graphical user interface.
How to Use the SQL Export Utility
The SQL Export Utility presents a tree structure that displays all the
databases and user-created tables in SQL Server. To use the SQL Export Utility,
you first select the name of the SQL Server you want to connect to and click
Connect. The SQL Export Utility then prompts for your SQL Server login ID and
password. After you successfully log on to SQL Server, the SQL Export Utility
lists all the databases you have access rights to. Clicking a database name
causes the SQL Export Utility to expand the tree structure and list all the
user-created tables in the selected database. To export a table, you can select
the table from the tree structure and click Export, or you can simply
double-click the table name in the tree list. The SQL Export Utility then
displays a File Save dialog that lets you select a name and destination path for
the exported file. Clicking Save calls the new SQL-DMO BulkCopy object to export
the file.
Quick Review of SQL-DMO
Because I described DMO in detail last month, I'll only briefly review DMO
here. DMO is a set of 32-bit Object Linking and Embedding (OLE) objects that
enable program access to SQL Server's management functions. DMO is part of the
Distributed Management Framework that Microsoft created 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
from only 32-bit client applications.
When you first install SQL Server's 32-bit client utilities, SQL Server copies the files that provide the basic support for DMO to your client system. To access DMO from VB, you need to reference the SQL-DMO type library.
To reference the SQL OLE type library, select References from VB's Tools menu to display the References dialog. Scroll through the References dialog until you see the Microsoft SQLOLE Object Library. Select this entry to add the sqlole32.tlb file to VB's IDE. (The SQL Server DMO functions are in sqlole32.dll.)
Adding a Reference Type Library to VB is different from adding an OCX or ActiveX control. When you add OCX and ActiveX controls to VB, an icon represents the control in VB's toolbox. Adding the SQL OLE Object Library to VB doesn't add any objects to the VB toolbox. However, you can use VB's Object Browser to see the methods and properties of the SQL OLE Object. Screen 2 shows a view of the SQL OLE object in VB's Object Browser.
Inside the SQL Export Utility
Now let's look inside the SQL Export Utility. Every SQL-DMO program must
first create an instance of the SQL Server DMO OLE object. Because the SQL
Export Utility needs to 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 need to use it to connect to SQL Server. In the SQL Export Utility application, you select a SQL Server name from a pulldown menu (as in Screen 1), and click Connect. The SQL Export Utility then displays the SQL Server Login dialog shown in Screen 3. You enter values for User ID and Password, and click OK to execute the command_ok_click subroutine shown in Listing 1.
The SQL Export Utility uses VB's TreeView control to present an Explorer-type, hierarchical tree of the databases and tables in SQL Server. To add nodes to the TreeView control, the subroutine must first create a Node object, which occurs at callout A in Listing 1.
After command_ok_click creates the Node object, the subroutine uses the SQL-DMO Connect method (at B in Listing 1) to connect to SQL Server. The SQL Server ID comes from the SQL Server pulldown menu shown in Screen 1, and the user ID and password come from the SQL Server Login window shown in Screen 3.
After the Connect method executes, the command_ok_click subroutine tests 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 the Connect method's execution, a message box displays to let you retry the connection or cancel the current connection attempt. The retry option runs the Connect method with no changes to the login information. If you cancel the attempt, the subroutine ends and you return to the main window of the SQL Export Utility (Screen 1). There you can select a different SQL Server or provide a new user ID and password.
After the Connect method has successfully completed, the command_ok_click subroutine retrieves all the databases and tables on the connected SQL Server and adds them to the TreeView control. The Add method at C in Listing 1 adds a root node to the TreeView control. The first parameter of this method designates the parent node of the node being added. (This parameter is blank for the root node.) The second parameter specifies the identifying key of the node being added. For the first node, I used a key value of "Root". The third parameter specifies the text to be displayed in the control. For the root node of the SQL Export Utility, this text is the name of the SQL Server system from the pulldown menu shown in Screen 1. The last parameter specifies the bitmap to be displayed. The value "closed" uses a closed-folder bitmap.
The code at D in Listing 1 shows how to get a list of the SQL Server databases and tables with SQL-DMO. The Databases property in oSQLServer contains a collection of the database names for the SQL Server. Likewise, the Tables property of each SQLServer. Databases object contains a collection of table names.
Starting at D, a pair of nested For Each operations loops through all the table names for every database of the SQL Server. The Add method adds each name to the TreeView control. The parent node for the database names is set to the root key, the SQL Server name. The parent name for each table node is set to the name of the corresponding database.
After the command_ok_click subroutine finishes, the SQL Export Utility displays the main form shown in Screen 1. The TreeView control shows the names of each SQL Server database. Clicking a database name expands the TreeView list to display the tables for each database.
To export a table, select its name and click Export, or simply double-click the table name in the TreeView list. Either action executes the command_export_click subroutine shown in Listing 2.
At A in Listing 2, the subroutine creates a SQL Server BulkCopy object. The BulkCopy object contains the parameters that control the export process.
Next, the subroutine uses VB's CommonDialog ShowSave method to display a Save File dialog. This dialog lets you enter the destination filename and path for the exported file.
After you click Save in the dialog, the code at B in Listing 2 sets the filename property of SQL Server's BulkCopy object to the name of the table selected in the TreeView control. Next, the subroutine runs the ExportData method of the SQLServer.Databases Table object to perform the export.
The ExportData method takes the BulkCopy object as a parameter and uses the database and table node names to retrieve the appropriate DMO table object to be exported. If an error occurs, the sqldmoerror function displays the error number and text in a message box.
For More Information
SQL-DMO provides access to more than just SQL server management
functions--you can also use SQL-DMO to access and export data in SQL Server. You
can build a variety of powerful custom management tools for SQL Server with
SQL-DMO's 600+ functions and 1000+ properties.
The utilities presented in this VB Solutions column and in last month's column just scratch the surface of what you can do with SQL Server and DMO. For more information, refer to Microsoft SQL Server Programming SQL Distributed Objects, and the Microsoft Developer Network CD-ROM, which contains several articles and programming examples about using DMO.
Reader Feedback
In response to my request for your input to "VB Solutions,"
readers have pointed out a couple of anomalies in the code for my November
utility, Network Security Monitor. The NetServerEnum does not return the correct
result as written. It returns all machines on the network, not just the
NT machines. For example, one reader, L. J. Johnson explained that the code
returned his NT Workstation, NT Server, Windows for Workgroups, and Windows 95
workstations. Thank you for reading the article, trying out the code, and
telling me about the problem. You can find the fixes on our Web site at
http://www.winntmag.com.
Oops
Because of an editing error, Mark Russinovich's article, "Inside
the Difference Between Windows NT Workstation and Windows NT Server,"
November 1996, states that processes get more physical memory on Server than on
Workstation. The correct statement is that Workstation processes are flagged for
aggressive working set trimming if the system size is small or the executable
image of the process is marked for trimming. Also, as a clarification of
AFD.SYS's restriction of Workstations of two simultaneous file transfers: The
limit applies only to files transferred via Winsock.
