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"
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
ListView1.ListItems.Clear
Set oDb = moSqlServer.Databases(Combo1.Text)
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
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
ListView1.ListItems.Clear
Set oDb = moSqlServer.Databases(Combo1.Text)
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
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.
Private WithEvents moBulkCopy As SQLDMO.BulkCopy
Private Sub Command1_Click()
Dim oSqlServer As SQLDMO.SQLServer
Dim oTable As SQLDMO.Table
Dim lImportReply As Long
Set oSqlServer = New SQLDMO.SQLServer
oSqlServer.Connect "MYSERVER"
Set oTable = _
oSqlServer.Databases("MyDatabase").Tables("MyTable")
Set moBulkCopy = New SQLDMO.BulkCopy
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
lImportReply = oTable.ImportData(moBulkCopy)
MsgBox CStr(lImportReply) & " records imported"
Set moBulkCopy = Nothing
Set oTable = Nothing
Set oSqlServer = Nothing
End Sub
Private Sub moBulkCopy_RowsCopied(ByVal
Message As String,_
ByVal Rows As Long)
ProgressBar1.Value = (Rows
/ mlRecordsToProcess) * 100
End Sub
Private Sub moBulkCopy_BatchImported(ByVal
Message As String)
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.
