Exporting Data From SQL Server

Combine VB 4.0's TreeView control with SQLServer 6.5's DMO BulkCopy object for a simple file export utility

[Editor's Note: VB Solutions is about using Visual Basic to build avariety of solutions to specific business problems. This column doesn't teachyou 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'sfunctionality. Last month, I showed how to use DMO to create a custom managementtool for SQL Server. This month, I'll build on that foundation by showing youhow 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 SQLExport Utility is a graphical data export tool that lets you export data from aSQL Server table to a flat file. Exporting SQL Server data is important when youneed to transfer SQL Server tables and views to other applications and platformsthat can't access SQL Server directly. For instance, if you need to transfer SQLServer data to a mainframe or an AS/400, you must first export the SQL Serverdata and then run a file transfer program to upload the data to the host. SQLServer provides a command-line bulk copy program that's easy to operate, but itdoesn't have a graphical interface. This month's SQL Export Utility combines VB4.0's TreeView control with SQL Server 6.5's DMO BulkCopy object to provide asimple 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 thedatabases 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 clickConnect. The SQL Export Utility then prompts for your SQL Server login ID andpassword. After you successfully log on to SQL Server, the SQL Export Utilitylists all the databases you have access rights to. Clicking a database namecauses the SQL Export Utility to expand the tree structure and list all theuser-created tables in the selected database. To export a table, you can selectthe table from the tree structure and click Export, or you can simplydouble-click the table name in the tree list. The SQL Export Utility thendisplays a File Save dialog that lets you select a name and destination path forthe exported file. Clicking Save calls the new SQL-DMO BulkCopy object to exportthe file.

Quick Review of SQL-DMO
Because I described DMO in detail last month, I'll only briefly review DMOhere. DMO is a set of 32-bit Object Linking and Embedding (OLE) objects thatenable program access to SQL Server's management functions. DMO is part of theDistributed Management Framework that Microsoft created to facilitate the use ofSQL Server in a distributed environment. The Distributed Management Frameworkextends the power of SQL Server's management function to all the clients in thenetwork. Because SQL Server implements the DMO as OLE objects, you can use themfrom only 32-bit client applications.

When you first install SQL Server's 32-bit client utilities, SQL Servercopies 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 Toolsmenu to display the References dialog. Scroll through the References dialoguntil you see the Microsoft SQLOLE Object Library. Select this entry to add thesqlole32.tlb file to VB's IDE. (The SQL Server DMO functions are insqlole32.dll.)

Adding a Reference Type Library to VB is different from adding an OCX orActiveX control. When you add OCX and ActiveX controls to VB, an icon representsthe control in VB's toolbox. Adding the SQL OLE Object Library to VB doesn't addany objects to the VB toolbox. However, you can use VB's Object Browser to seethe methods and properties of the SQL OLE Object. Screen 2 shows a view of theSQL 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 mustfirst create an instance of the SQL Server DMO OLE object. Because the SQLExport 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 thefollowing code:

Public oSQLServer As New SQLOLE.SQLServer

After you create a SQL-DMO object, you need to use it to connect to SQLServer. In the SQL Export Utility application, you select a SQL Server name froma pulldown menu (as in Screen 1), and click Connect. The SQL Export Utility thendisplays the SQL Server Login dialog shown in Screen 3. You enter values forUser ID and Password, and click OK to execute the command_ok_click subroutineshown in Listing 1.

The SQL Export Utility uses VB's TreeView control to present anExplorer-type, hierarchical tree of the databases and tables in SQL Server. Toadd nodes to the TreeView control, the subroutine must first create a Nodeobject, which occurs at callout A in Listing 1.

After command_ok_click creates the Node object, the subroutine uses theSQL-DMO Connect method (at B in Listing 1) to connect to SQL Server. The SQLServer ID comes from the SQL Server pulldown menu shown in Screen 1, and theuser ID and password come from the SQL Server Login window shown in Screen 3.

After the Connect method executes, the command_ok_click subroutine testsfor a successful connection. In Listing 1, the Err.Number property contains anyError codes the SQL-DMO Connect method generates.

If an error occurs during the Connect method's execution, a message boxdisplays to let you retry the connection or cancel the current connectionattempt. The retry option runs the Connect method with no changes to the logininformation. If you cancel the attempt, the subroutine ends and you return tothe main window of the SQL Export Utility (Screen 1). There you can select adifferent SQL Server or provide a new user ID and password.

After the Connect method has successfully completed, the command_ok_clicksubroutine retrieves all the databases and tables on the connected SQL Serverand adds them to the TreeView control. The Add method at C in Listing 1 adds aroot node to the TreeView control. The first parameter of this method designatesthe parent node of the node being added. (This parameter is blank for the rootnode.) The second parameter specifies the identifying key of the node beingadded. For the first node, I used a key value of "Root". The thirdparameter specifies the text to be displayed in the control. For the root nodeof the SQL Export Utility, this text is the name of the SQL Server system fromthe pulldown menu shown in Screen 1. The last parameter specifies the bitmap tobe 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 Serverdatabases and tables with SQL-DMO. The Databases property in oSQLServer containsa collection of the database names for the SQL Server. Likewise, the Tablesproperty of each SQLServer. Databases object contains a collection of tablenames.

Starting at D, a pair of nested For Each operations loops through all thetable names for every database of the SQL Server. The Add method adds each nameto the TreeView control. The parent node for the database names is set to theroot key, the SQL Server name. The parent name for each table node is set to thename of the corresponding database.

After the command_ok_click subroutine finishes, the SQL Export Utilitydisplays the main form shown in Screen 1. The TreeView control shows the namesof each SQL Server database. Clicking a database name expands the TreeView listto display the tables for each database.

To export a table, select its name and click Export, or simply double-clickthe table name in the TreeView list. Either action executes thecommand_export_click subroutine shown in Listing 2.

At A in Listing 2, the subroutine creates a SQL Server BulkCopy object. TheBulkCopy object contains the parameters that control the export process.

Next, the subroutine uses VB's CommonDialog ShowSave method to display aSave File dialog. This dialog lets you enter the destination filename and pathfor the exported file.

After you click Save in the dialog, the code at B in Listing 2 sets thefilename property of SQL Server's BulkCopy object to the name of the tableselected in the TreeView control. Next, the subroutine runs the ExportDatamethod of the SQLServer.Databases Table object to perform the export.

The ExportData method takes the BulkCopy object as a parameter and uses thedatabase and table node names to retrieve the appropriate DMO table object to beexported. If an error occurs, the sqldmoerror function displays the error numberand text in a message box.

For More Information
SQL-DMO provides access to more than just SQL server managementfunctions--you can also use SQL-DMO to access and export data in SQL Server. Youcan build a variety of powerful custom management tools for SQL Server withSQL-DMO's 600+ functions and 1000+ properties.

The utilities presented in this VB Solutions column and in last month'scolumn just scratch the surface of what you can do with SQL Server and DMO. Formore information, refer to Microsoft SQL Server Programming SQLDistributed 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 Novemberutility, Network Security Monitor. The NetServerEnum does not return the correctresult as written. It returns all machines on the network, not just theNT machines. For example, one reader, L. J. Johnson explained that the codereturned his NT Workstation, NT Server, Windows for Workgroups, and Windows 95workstations. Thank you for reading the article, trying out the code, andtelling me about the problem. You can find the fixes on our Web site athttp://www.winntmag.com.

Oops
Because of an editing error, Mark Russinovich's article, "Insidethe Difference Between Windows NT Workstation and Windows NT Server,"November 1996, states that processes get more physical memory on Server than onWorkstation. The correct statement is that Workstation processes are flagged foraggressive working set trimming if the system size is small or the executableimage of the process is marked for trimming. Also, as a clarification ofAFD.SYS's restriction of Workstations of two simultaneous file transfers: Thelimit applies only to files transferred via Winsock.