Administering SQL Server with Visual Basic

This column appeared in the October 1999 edition of EXE. It discusses the Data Management Objects ActiveX library that SQL Server exposes to allow for administration tasks to be performed from such languages as Visual Basic.

Click here to download sample code

---

Last month I introduced the ADOX library that makes it debut with version 2.1 of ADO. ADOX is a means of providing the developer with the facility to manipulate schemas in different database back-ends, albeit with a fairly limited implementation this time around. This month I am discussing SQL Server Data Management Objects (SQL-DMO), a specific library for SQL Server developers that offers control over many aspects of SQL Server functionality. The library has been around since SQL Server 6.0, and has been enhanced with each subsequent release. To demonstrate the amount of control that this library offers I’ll just mention that the Enterprise Manager application (the 6.x and the 7.0 version) does its work through DMO calls.

Visual Studio 6 Enterprise Edition ships with a developer-specific version of BackOffice that includes SQL Server 6.5. In this article I’m actually using SQL Server 7.0 as my discussion subject because it makes sense to address the wider set of features that this newer product has to offer. For any developers who only have access to version 6.5 and want to have a go then I will just point out one important difference between the two implementations. In SQL Server 6.5 the Data Management Object library is referenced as SQLOLE, for example:

Dim oDatabase As SQLOLE.Database

Whereas SQL Server 7.0 has changed the reference name to SQLDMO, hence

Dim oDatabase As SQLDMO.Database

The SQL-DMO runtime files are installed along with the SQL Server client utilities so this would be a requisite for distributing your application. When starting a new project you should include a reference to ‘Microsoft SQLDMO Object Library’. SQL Server itself also contains additional stored procedures to provide support for this technology; these are installed automatically along with the core product but should they ever need reinstalling the setup script sqldmo.sql exists within the SQL Server installation’s Install folder.

One of the most important objects within SQL-DMO is the SQLServer object, through which it is necessary to logon to the SQL Server itself. Depending upon your own security level it is possible to perform most key operations, such as starting and stopping the service, creating a new database, and adding new users. Initialising the object is a simple matter, for example

Dim oSqlServer as SQLDMO.SQLServer
Set oSqlServer = New SQLDMO.SQLServer
oSqlServer.Connect "MyServer"

‘ Do something, then..

oSqlServer.Disconnect
Set oSqlServer = Nothing

Before delving too much into the ways in which the most likely tasks are performed I’d just like to stay with the SQLServer object itself and discuss some of the various useful pieces of information that it offers. When you are writing applications of an administrative nature there is usually a need to know whether the current user has the necessary levels of permissions to perform a certain action. This can apply both at the SQL Server level and the operating system level which, in many cases, would entail having to make calls to the Win32 API. The designers of the SQLServer object have tried to predict the most likely pieces of information that will be required and have made them available. Some of the ones that I would choose as being the most useful would be:

IsNTGroupMember: Boolean function which will determine whether a user is a member of a specific NT group.

IsOS: Used to check which version of Windows is present.

LinkedServers: returns a list of linked servers

There are several other functions which will return a Boolean value denoting whether a user has membership of any of the server roles that form part of the SQL Server 7.0 security model. Elsewhere, within the Application object, the ListAvailableSQLServers methods returns a list of all running SQL Servers that are visible within the scope of the network.

Accessing Database and Table Information

SQL Server specific items are exposed through collections, such as the Databases collection and the Tables collection. As is typical, these collections can either be enumerated with a For Each..Next construct, or can be accessed directly, such as

Set oPubsDatabase = oSqlServer.Databases(“pubs”)

To show how this database and table information can be obtained and displayed I have built a sample application (see end of column for download details). Figure 1 shows the layout of the application: a dropdown listbox at the top of the screen displays all of the databases that can be found on the current server, and then various pieces of information for each table within that database are displayed.

Figure 1: Example SQL-DMO program

With the exception of changing the server name in the SQLServer.Connect method this should work for any SQL Server 7.0 installation (he says!).

For the table details I have chosen to use a ListView control (set to Report View mode). The five pieces of information that are being displayed about each table are the name, the owner, the number of rows of data held by the table, the date that it was created, and finally the amount of data space used. Listing 1 shows how the list of databases are initially obtained and passed into a ListBox control.

Private Sub Combo1_Click()
  Dim oDb As SQLDMO.Database
  Dim oTable As SQLDMO.Table
  Dim liObject As ListItem

  'Empty the ListView control
  ListView1.ListItems.Clear

  ' Get a reference to the selected database
  Set oDb = moSqlServer.Databases(Combo1.Text)

  ' Enumerate each table and display info in ListView control
  For Each oTable In oDb.Tables

    Set liObject = ListView1.ListItems.Add(, , oTable.Name)
    liObject.ListSubItems.Add , , oTable.Owner
    liObject.ListSubItems.Add , , Format(oTable.Rows, "#,##0")
    liObject.ListSubItems.Add , , Format(Left(oTable.CreateDate, _
      10), "Long date")
    liObject.ListSubItems.Add , , Format(oTable.DataSpaceUsed, _
      "#,##0") & " KB"
    Set liObject = Nothing

  Next

  ' Release the Database object reference
  Set oDb = Nothing

End Sub

Listing 1: Obtain list of databases in a SQL Server

Listing 2 then shows how a click event on the ListBox causes a ListView control to be emptied and then populated with several pieces of information about each table found within that database.

Private Sub Combo1_Click()

  Dim oDb As SQLDMO.Database
  Dim oTable As SQLDMO.Table
  Dim liObject As ListItem

  'Empty the ListView control
  ListView1.ListItems.Clear

  ' Get a reference to the selected database
  Set oDb = moSqlServer.Databases(Combo1.Text)

  ' Enumerate each table and display info in ListView control
  For Each oTable In oDb.Tables
    Set liObject = ListView1.ListItems.Add(, , oTable.Name)
    liObject.ListSubItems.Add , , oTable.Owner
    liObject.ListSubItems.Add , , Format(oTable.Rows, "#,##0")
    liObject.ListSubItems.Add , , Format(Left(oTable.CreateDate,_
      10), "Long date")
    liObject.ListSubItems.Add , , Format(oTable.DataSpaceUsed, _
      "#,##0") & " KB"
    Set liObject = Nothing  
  Next

  ' Release the Database object reference
  Set oDb = Nothing

End Sub

Listing 2: Displaying tables within a database

Data Transfer

One of the more likely attractions of SQL-DMO for the developer is the ability to automate data transfer. SQL Server offers both the Bulk Copy Program and the newer Data Transformation Services, and both of these technologies have corresponding SQL-DMO objects. In the case of the bulk copy routines, a standalone (i.e. not actually part of the hierarchy) BulkCopy object is available to provide the definition for the data to be imported or exported. This BulkCopy object is then provided as a parameter to the ImportData and ExportData methods of a Table object. Listing 3 shows the code for a typical bulk copy operation.

' Module level declaration
Private WithEvents moBulkCopy As SQLDMO.BulkCopy

Private Sub Command1_Click()
  Dim oSqlServer As SQLDMO.SQLServer
  Dim oTable As SQLDMO.Table
  Dim lImportReply As Long

  ' Initialise object references
  Set oSqlServer = New SQLDMO.SQLServer
  oSqlServer.Connect "MYSERVER"
  Set oTable = _
    oSqlServer.Databases("MyDatabase").Tables("MyTable")
  Set moBulkCopy = New SQLDMO.BulkCopy

  ' Initialise the oBulkCopy object
  moBulkCopy.DataFilePath = "c:\sqldmo\testdata.csv"
  moBulkCopy.UseBulkCopyOption = True
  moBulkCopy.SuspendIndexing = True
  moBulkCopy.TruncateLog = True
  moBulkCopy.ErrorFilePath = "c:\sqldmo\bulkcopy.err"
  moBulkCopy.ImportRowsPerBatch = 1000
  moBulkCopy.ColumnDelimiter = ","
  moBulkCopy.RowDelimiter = Chr(13) + Chr(10)
  moBulkCopy.DataFileType = SQLDMODataFile_SpecialDelimitedChar

  ' Run the import job
  lImportReply = oTable.ImportData(moBulkCopy)
  MsgBox CStr(lImportReply) & " records imported"

  ' Release references
  Set moBulkCopy = Nothing
  Set oTable = Nothing
  Set oSqlServer = Nothing
End Sub

Private Sub moBulkCopy_RowsCopied(ByVal Message As String,_
ByVal Rows As Long)
  ' Assume that mlRecordsToProcess has been set somewhere
  ProgressBar1.Value = (Rows / mlRecordsToProcess) * 100
End Sub

Private Sub moBulkCopy_BatchImported(ByVal Message As String)
  ' Always supply an event handler
End Sub

Listing 3: SQL-DMO bulk copy operation

Note that this example also makes use of the events that are made available by the BulkCopy object, allowing us to give some visual feedback of progress to the user. If you are using WithEvents in your object declaration for any of the SQL-DMO objects then Microsoft do strongly recommend that you supply a specific (i.e. non-empty) Sub for each event, even if you don’t need it. An unneeded Sub merely needs an Exit Sub statement to be retained by the compiler. Incidentally when I was writing some production code using the bulk copy object I sometimes encountered an EXCEPTION_ACCESS_VIOLATION error that caused the import job to fail. If anybody gets this under SQL Server 7.0 then the good news is that service pack 1 fixes the fault.

Can you use SQL-DMO to access and manipulate data within SQL Server tables? Well, yes you can, but this probably isn’t the best way to do it. In applications that I have written it is sometimes necessary to update tables with fresh data, and this is easily performed through a series of Execute commands. Anything more serious than this requires a more appropriate technology such as ADO. The SQL-DMO library offers a very efficient mechanism for manipulating all kind of SQL Server objects. If your project is only SQL Server based then this is a very appropriate means of performing administrative tasks. If, however, you need a common set of functionality then you will eventually be better off going with ADOX but this is almost certainly too immature a technology at this time to warrant the effort.