David Mendlen
Technical Product Manager for Visual Studio
Microsoft Corporation
October 1997
From Regional Director Magazine
Click to copy the SQLOLE_EXE sample files.
The flexibility and functionality of the Microsoft® SQL Server™ Enterprise Manager is unmatched in the enterprise database market. This functionality is not limited to the administrator using it. The power of the Enterprise Manager is available to the Visual Basic® programmer using automation. Thus the problem: Design a reusable ActiveX™ control that exposes the functionality of SQL Server.
Figure 1. New functionality to create and delete devices
The solution involves combining the SQL Distributed Management Objects (DMO) capabilities of Microsoft SQL Server version 6.0 and 6.5 with the ActiveX control creation functionality of Microsoft Visual Basic version 5.0. With this solution, a developer can drop an ActiveX control onto a Visual Basic form, a Visual InterDev™ Web page, or any other ActiveX control–capable environment. The newly developed control has the ability to display all of the elements that are visible in the Enterprise Manager. Additionally, I've included functionality to create and delete devices and databases (Figure 1). The solution can easily be extended to perform a variety of other tasks including all the functionality provided in the enterprise manager. It's very easy to create functionality to add, delete, and change tables, views, stored procedures, rules, defaults, and user-defined types.
Why do we care about SQL DMO? The programming components in Microsoft SQL Server 6.0 let you develop sophisticated client/server applications that tap the power of Microsoft SQL Server. SQL DMO exposes objects, properties, methods, and collections that are used to write scripts and programs that can administer multiple SQL Servers across a distributed network. This model is not yet another data access alternative like ADO, DAO, and so on. SQL DMO allows you to create database administration functions. With these functions you could publish database statistics on an intranet.
In order to access the SQL DMO objects in Visual Basic you need to install the SQL Server client objects on your development machine. It will install and register the SQL DMO objects. It is important to note that users of solutions that you build with SQL DMO must have SQL DMO installed through the same process.
The motivation for this solution was a dare. A Microsoft BackOffice Product Manager told me that the Enterprise Manager was written in SQL DMO. When I argued that it was impossible to do, the Product Manager dared me to build my own. Before we get into the ActiveX control, let's take a closer look at SQL DMO.
The core of the SQLDMO object model is the SQLServer object. For each SQL Server you want to connect to, you create an empty SQLServer object, then you connect the SQLServer object to a running SQL Server.
The SQL DMO application name is SQLOLE:
CreateObject("SQLOLE.SQLSERVER")
SQL DMO utilizes vtable-binding or early binding. You can also use Dispatch ID binding (dispID-binding) which uses slower performance than using the New keyword but faster than the generic Object type.
Dim oSQLServer As Object
Set oSQLServer = CreateObject ("SQLOLE.SQLServer")
This method is called late binding using OLE IDispatch.
After creating a new, empty SQLServer object, you need to connect that object to a running SQL Server using the Connect method of the new SQLServer object. You pass the following parameters to the Connect method:
Dim SQLObject As SQLOLE.SQLServer
' initialize the object and connect
Set SQLObject=CreateObject ("Sqlole.SQLServer")
SQLObject.Connect "MyServer", "SA", "Password"
You can iterate through the list of databases (from the SQL Server object) with the databases collection using a For . . Each statement.
For Each database in SQLObject.Databases
Debug.Print database.name
Next
' Disconnect SQLObject from SQL Server
SQLObject.DisConnect
' Good programming practice; clean up objects as we go
Set SQLObject = Nothing
Figure 2 illustrates the SQL DMO object model.
Figure 2. The SQL DMO object Model
Figure 3. The SQL MDO server-side machine
To use this solution, you need two computers. One is the server running Windows NT® and SQL Server. The other runs the client application created by you. Simply drop the control on a Visual Basic form and set the three critical properties: SQL Server, login ID, and password. In order to access the SQL DMO objects on your development machine, you must have installed the SQL Server client components. The client machine could be running Windows® 95 or Windows NT.
Figure 4. The SQL DMO client-side machine
In order for the ActiveX control to display all of the visual elements of the SQL Enterprise Manager as well as creating and deleting devices, we only need to talk to a few of the SQL DMO objects. This is done with the following code:
Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
The first step is to connect to SQL Server. You simply provide the server name, a login ID, and a password:
Set objSQLServer = CreateObject("SQLOLE.SQLserver")
'Connect to SQL Server
objSQLServer.Connect ServerName:=pvtSQLServer, login:=pvtLoginID, Password:=pvtPassword
.
.
.
Next we connect to the parent object of the connected SQL Server—the application object:
Dim SQLApp As SQLOLE.Application
Set SQLApp = objSQLServer.Application
Now we can iterate through the collection of SQL Servers, which is obtained through the SQLServers collection. A For . . Each statement works well for this type of thing.
Dim sServer As SQLOLE.SQLServer
For Each sServer In SQLApp.SQLServers
Add the SQL Server name to the tree view control. SServer.Name is the name of the SQL Server:
Set nodx = TreeView1.Nodes.Add("RTGroups", tvwChild, sServer.Name, sServer.Name, 3)
.
.
.
Now we can iterate through each device on the current SQL Server. First we refresh the collection of devices with the Refresh method.
Dim device As SQLOLE.device
sServer.Devices.Refresh
Again we can iterate through the collection of devices on the current SQL Server utilizing a For . . Each statement:
For Each device In sServer.Devices
Devices come in two flavors: database devices and dump devices. We determine the database type and add the device to the treeview control:
If device.Type = 0 Then 'Database Devices
Set nodx = TreeView1.Nodes.Add("RTDevices", tvwChild, "dv" & device.Name, device.Name, 5)
Else
Set nodx = TreeView1.Nodes.Add("RTDump Devices", tvwChild, device.Name, device.Name, 6)
End If
You access the database collection from the current device. Again the first step is to refresh the collection:
Dim Database As SQLOLE.Database
sServer.Databases.Refresh
Accessing the databases collection is done a little differently—we use the ListDatabases collection. I added a function that takes the database name as a parameter and adds the database and its child objects to the tree:
For Each Database In device.ListDatabases
AddDatabaseToTree Database.Name
Next
Finally, we iterate through the list of logins, and add them to the treeview:
Dim login As SQLOLE.login
For Each login In sServer.Logins
Set nodx = TreeView1.Nodes.Add("Logins", tvwChild, login.Name, login.Name, 8)
Next
Next
Don't forget to disconnect from SQL Server:
objSQLServer.DisConnec
As you can see, communicating with the DMO objects is as easy as manipulating any other Visual Basic components. This example can easily be extended to surface other tasks. For example, to add a stored procedure to SQL Server in code you would do the following:
Sub AddStoredProcedureToDatabase(Database, StoredProcedureName, StoredProcedureText)
First we connect to the appropriate database in SQL Server. Then we connect to the StoredProcedures collection. I chose to do it all in one line.
Dim mySPs As SQLOLE.StoredProcedures
Set mySPs = objSQLServer.Databases.Item(Database).StoredProcedures
Now we create a new StoredProcedure object and set its properties. A stored procedure has two critical properties: its name and the SQL statement that you want executed. The SQL statement is the .TEXT property. You notice that I've filled in the literal string "Create Procedure" and appended the stored procedure name as well as the literal string "as". Combined with StoredProcedureText, the result is something like "Create Procedure Test as Select * from authors."
Dim mySP As SQLOLE.StoredProcedure
Set mySP = CreateObject("SQLOLE.storedprocedure")
mySP.Name = StoredProcedureName
mySP.Text = "Create Procedure " & mySP.Name & " as " & StoredProcedureText
Now all we need to do is add the newly created stored procedure to the stored procedure collection we connected to earlier:
mySPs.Add mySP
Don't forget to clean up:
Set mySP = Nothing
Set mySPs = Nothing
objSQLServer.DisConnect
Step 1: Create a new Standard .EXE in Visual Basic from the New Project window as shown in Figure 5.
Figure 5. The New Project window
Step 2: Add the ActiveX SQL component to the Toolbox. Right-click on the toolbox and select Components… Select the ActiveX SQL Library from the list, as shown in Figure 6. If the component isn't yet registered, click the Browse… button to navigate to the directory of the ActiveXSQL.OCX file. Then click OK.
Figure 6. The Components dialog box
Step 3: Draw an instance of the ActiveXSQL control onto your form, similar to the one shown in Figure 7
Figure 7. Drawing an instance of the ActiveXSQL control
Step 4: Right-click on the control and select Properties. Fill out the three required fields and click OK, as shown in Figure 8.
Figure 8. Filling out the required fields
Step 5: Run it!
Figure 9. The results
I hope you enjoyed this example. As you can imagine, the possibilities are endless. There are a few resources that you should turn to for more information about SQL DMO. Try the following three Web sites.
http://www.bstone.com/SQLUG/SampleCode/samples.htm#DMO
http://www.windows.com/KB/Articles/q154/0/11.htm
http://www.ntmag.com/issues/jun96/sql.htm
David Mendlen is the director of development for Ameritech Cellular. David is a Microsoft Certified Solution Developer and a certified trainer in Visual Basic and Windows 95. He is also the founder and president of the Chicago Corporate Visual Basic User Group. David recently worked at Microsoft in Redmond on solutions that will ship in future products and he is a frequent contributor to the Cobb Group journals.