Ken Bergmann
Microsoft Developer Network Technology Group
November 12, 1995
Click to view or copy the files for the SQLDMO sample application.
This technical article illuminates the functionality present in the SQL-DMO server architecture. It explains how an OLE Automation controller can use the functionality provided by SQL-DMO to automate and customize Microsoft® SQL Server objects or processes. This article assumes knowledge of how to use an in-process OLE server from Visual Basic®. It also assumes basic knowledge of the Microsoft SQL Server Relational Database System.
SQL-DMO is the abbreviation for the Microsoft® SQL Server Distributed Management Object. SQL-DMO is a Component Object Model (COM) object representation of the standard SQL Server internal objects. That means that the SQL-DMO is an in-process OLE server. This OLE server exposes representations of Microsoft SQL Server database objects (tables, stored procedures, server properties, and so on), many of which are usually modified through Transact-SQL (T-SQL). Using these new OLE server objects, you can now manipulate any SQL Server database object from any OLE Automation controller. Specifically, this document uses the Basic language in examples and illustrations.
SQL-DMO is physically a set of dynamic-link libraries (DLLs) with a type library. The normal setup registers the information in the type library for use by OLE Automation clients. If you do have problems during the installation, the type library information can be registered using an included registry (.REG) file. Once you have the libraries properly installed in your environment, SQL-DMO can be used just like any other OLE server. This article provides information about the object model that makes up SQL-DMO and how to use the objects and functionality exposed in the OLE server.
The SQL-DMO OLE server model is very robust and includes an intelligent use of collection objects. Because a good interface model can be more important than the functionality it exposes, the clean design of this model is one of the best reasons to consider using SQL-DMO for administrative tasks. Figure 1 shows the top level of the model.
Figure 1. The Application object and other system-level objects in the SQL-DMO model
The top-level objects are objects that are available in other parts of the model. For example, several objects have a Names collection, and almost every object has a collection of Property objects exposed. So, for simplicity, I will say that the objects in Figure 1, from the Backup object on down, are exposed objects that are defined but not required to be explicitly instantiated. The difference is that by instantiating a SQL Server object, you have access to a collection of Database objects. On the other hand, only at certain times can you use a Registry, Language, or Permission object. This is usually because these objects are normally returned from or passed to other methods of other objects.
The SQL Server object is the most heavily used of the SQL-DMO objects. The SQL Server's list of exposed objects is also one of the largest in the whole model. Essentially, everything that can be configured or populated in a SQL Server above the database level is exposed here. As you become familiar with the model, notice the heavy use of object collections (Figure 2). This type of clean exposure is a large part of what is so beautiful about this model.
Figure 2. The Microsoft SQL Server-level objects in the SQL-DMO model
Now that you have all of the different objects outlined, let's see what you can accomplish using a few of these objects from Visual Basic®.
There can be many uses for SQL-DMO that will make it easier to manage a client-server system. In some situations, the everyday processing of a system may be easier to create by building around these objects, instead of providing custom processing. An example would be using the Task objects to execute batch processors instead of using the /AT command line or similar schedulers. By using these objects instead of SQL or command-line interfaces, you can use the functionality of the built-in Event Log to track successes and failures, or you can use the pager and e-mail notification processes that are also built into Microsoft SQL Server. Neither of these services (pager and e-mail) requires you as the developer to provide much, if anything.
But now, let's see some code. This piece of code shows how to manipulate the Properties collection of the primary SQL Server object. Feel free to lick your lips over the plethora of information you can get with just a few lines.
Dim oSQLServer As New SQLOLE.SQLServer
oSQLServer.DisConnect 'Not required, just good form.
Err.Number = 0
oSQLServer.Connect CStr(txtServer), CStr(txtLogin), CStr(txtPassword)
With txtOut
If Err.Number = 0 Then
.Text = ""
.Text = "ServerName : " & oSQLServer.Name & NL
.Text = .Text & "HostName : " & oSQLServer.HostName & NL
.Text = .Text & "Language : " & oSQLServer.Language & NL
.Text = .Text & "Login : " & oSQLServer.Login & NL
.Text = .Text & "Password : " & oSQLServer.Password & NL
.Text = .Text & "LoginSecure : " & oSQLServer.LoginSecure & NL
.Text = .Text & "SaLogin : " & oSQLServer.SaLogin & NL
.Text = .Text & "TrueLogin : " & oSQLServer.TrueLogin & NL
.Text = .Text & "TrueName : " & oSQLServer.TrueName & NL
.Text = .Text & "QueryTimeout : " & oSQLServer.QueryTimeout & NL
.Text = .Text & "CommandTerminator : " & oSQLServer.CommandTerminator & NL
.Text = .Text & "AutoReConnect : " & oSQLServer.AutoReConnect
Else
lblStatus = Err.Source & " Error " & Err.Number - vbObjectError & ":"
lblStatus = lblStatus & " " & Err.Description
End If
End With
That's it! I kept it simple and just spit the data out into a text box, but you get the idea. Let's upgrade things a bit and get out of first gear. The following snippet assumes you still have the server variable in scope. It uses a ListView control to show the databases that are in the current server. If you aren't familiar with the ListView control, the important lines are in bold. They show you how to walk through the collection of databases for a SQL Server object.
Dim oTask As Task 'Notice the specific object names.
Dim oDB As Database 'Early binding, what a concept.
Dim liTemp As ListItem
Dim chTemp As ColumnHeader
Dim iIndex As Integer
Err.Number = 0
With lvDatabase
If Err.Number = 0 Then
.ListItems.Clear
Set chTemp = .ColumnHeaders.Add(, "Name", "Name", 800)
Set chTemp = .ColumnHeaders.Add(, "Size", "Size", 350)
Set chTemp = .ColumnHeaders.Add(, "SpaceAvailable", "SpaceAvailable",1010)
Set chTemp = .ColumnHeaders.Add(, "DataSpaceUsage", "DataSize", 600)
Set chTemp = .ColumnHeaders.Add(, "IndexSpaceUsage", "IndexSize", 600)
Set chTemp = .ColumnHeaders.Add(, "SystemObject", "System", 420)
Set chTemp = .ColumnHeaders.Add(, "CreateDate", "CreateDate", 1550)
For Each oDB In oSQLServer.Databases
Set liTemp = .ListItems.Add()
With liTemp
.Icon = 1
.SmallIcon = 1
.Text = oDB.Name
.SubItems(1) = oDB.Size
.SubItems(2) = Format$(oDB.SpaceAvailableInMB, "0.00") & " mb"
.SubItems(3) = Format$(oDB.DataSpaceUsage, "0.00") & " mb"
.SubItems(4) = Format$(oDB.IndexSpaceUsage, "0.00") & " mb"
.SubItems(5) = oDB.SystemObject
.SubItems(6) = oDB.CreateDate
End With
Next
End If
End With
Okay, so we have gone over the basics. Now let's use one of those generic, top-level objects that we talked about at the beginning. For grins, I will stay with the Permission object. This piece of code will walk through the Permissions collection for a particular database:
Dim pl As SQLObjectList 'In VB this provides early binding. I included
Dim p As Permission 'it so you could see the types.
Set db = ss.Databases("pubs")
Set pl = db.ListObjectPermissions() 'This is a function that returns a list.
For Each p In pl
'This walks through the list you obtained.
'For each object in the list, interrogate properties for that object.
'In this case, the objects in the list were Permission objects.
Debug.Print "Object Name : " & p.ObjectName
Debug.Print "Object Type Name : " & p.ObjectTypeName
Debug.Print "Privilege Type Name: " & p.PrivilegeTypeName
Debug.Print "Privilege Granted : " & p.Granted
Next
This code also shows an important concept in the use of SQL-DMO. The designers of this object model have exposed a cool construct called a list. A list is essentially a read-only collection. You can obtain lists from many places in the model. But in this case you get the whole collection returned as a static snapshot, not just a reference to a bunch of faraway objects. (Can you say speed, ladies and gents?) The difference is subtle, but can be important. Essentially, whenever you call a Listx method, you get a unique type of collection back. But be warned: When you use a list or enumeration that returns a list of DBObject objects, it may contain more than one type of object.
Moving on, I want to point out how some of the more-often-used methods can be called. This little routine will run through all the tables in a database and do some maintenance on each one. This is a simple example, but it shows how to do some common tasks:
Dim oTable As Table 'In VB this provides early binding.
'I included it so you could see the types.
Set db = ss.Databases("pubs")
For Each oTable In db.Tables
'This walks through the Tables collection.
oTable.CheckTable
oTable.ReCompileReferences
oTable.RecalcSpaceUsage
oTable.UpdateStatistics
Next
To show some more advanced code, this example generates scripts for every table in a database. I have set some flags on the types of script that are generated. Notice that these flags can be added together, much like the flags for other application programming interfaces (APIs) you may be familiar with. In this case, I have requested the script to contain the DROP statements that will ensure the table is dropped before attempting to recreate it. I have also requested the default options on a script, as well as to have headers, indexes, and any bindings included in the generated script. There are several other types of options that can be set for this, which means that your script generation can be highly customized.
Dim lScriptType As Long
Dim sFileName As String
Dim sNewTableName As String
Dim oTable As Table
Set db = ss.Databases("pubs")
For Each oTable In db.Tables
'This walks through the Tables collection.
lScriptType = SQLOLEScript_Drops + SQLOLEScript_Default + _
SQLOLEScript_IncludeHeaders + SQLOLEScript_Indexes + _
SQLOLEScript_Bindings
sFileName = Trim$(oTable.Name) & ".sql"
sNewTableName = "New" & Trim$(oTable.Name)
'This line generates the file
oTable.Script ScriptType:=lScriptType, _
ScriptFilePath:=sFileName, _
NewName:=sNewTableName
Next oTable
Following is an example of how to add a column to an existing table. This illustrates how modifications are done, as well as how new objects are created.
Dim oTable As Table
Dim oCol As Column
Set db = ss.Databases("pubs")
For Each oTable In db.Tables
'This walks through the Tables collection.
Set oCol = New Column
oCol.AllowNulls = True
oCol.Datatype = "varchar"
oCol.Length = 30
oCol.Name = "NewCol"
oTable.BeginAlter 'This starts the change process.
oTable.InsertColumn oCol, "" 'Say what you want done.
oTable.DoAlter 'Commit the changes when you are done.
Next oTable
Essentially, if you want to modify, let's say, a table, you call the BeginAlter method of the object, then perform your changes. When you are finished setting properties or options, you call DoAlter. Following this method commits to the server any changes made since executing BeginAlter. In this case, I was simply adding a column to a table. However, I could have been changing the options for an Alert or a Task. For most objects, modifying the properties directly performs the updates, but for the following objects the BeginAlter and DoAlter mechanisms must be used:
Because you may not know how to clean up after you have used these objects, I included the following two lines. It's pretty simple, but I am providing them just in case they were hard to find in the sample.
oSQLServer.DisConnect
oSQLServer.Close
That pretty much covers the essentials of how to use the SQL-DMO objects. Everything else is really just some variation on the techniques described and illustrated above. You can find a few different variations in the SQLDMO sample that accompanies this article.