ActiveX for Database Administration

By Bill Wunder

ActiveX can be used with SQL 2000 via a DTS ActiveX Scripting Task and within a SQLAgent job step. At a high level SQLAgent job are a simple single threaded special type of a DTS package. Granted you have to use a little imagination to get from the SQLAgent to DTS, but if you can make that connection you’ll be in a good place to see how any discussion of ActiveX for DTS can also be applied to a SQLAgent ActiveX Script job step. I’m going to ramble on a bit about ActiveX for DTS and suggest that everything that applies to a DTS ActiveX task also applies similarly to an SQLAgent ActiveX Script job step.

DTS ActiveX scripting can provide a different sort of flexibility in how SQL Server database maintenance or administrative tasks are carried out. DTS provides a pretty sophisticated interface to a number of technologies and some excellent workflow glue that can allow you to do cool things with very little coding or scripting – and the accompanying debugging. Even so, sometimes you need to do things to keep the SQL Servers happy and humming along that don’t quite fit any of the cookie cutter molds provided in DTS or elsewhere. When you need to do a little T-SQL then some file manipulation before copying some stored procedures between servers ActiveX combined with the DTS workflow capabilities make an elegant pair. Or if you need to do some task on a server to get it ready for a change, then move database objects or data from anther server once the target is ready for change DTS can prove to be an excellent tool. Of course, you could also use VBA or Windows Scripting Host to do these kinds of heterogeneous repetitive tasks but I can almost promise you that the DTS package will require less time and VB than the other approaches to develop or maintain once you are comfortable with DTS. While DTS does support some UI capabilities, a decent UI is generally better coded with Access or Visual Studio tools. DTS is best when used as a command shell, Scheduled Task (AT) or SQLAgent hosted utility. You can move variables to a DTS ActiveX Task as variables, table data, or even as file based content.  

A DTS ActiveX script can be very useful when you want to use SQLDMO or need a connection to two or more SQL Servers to complete an admin task or when you want to complete multiple steps that are difficult to link – perhaps two ActiveX scripts or any two or more steps that might be appropriate for DTS’s stable of tasks - from a Dynamic Property Task to a Bulk Load Task, Multi-phase  DataPump Task, or even a Copy SQL Server Objects Task.

ActiveX and Late Binding

All the late binding business means is that you wait until you are ready to use an object model until you are ready to use it. Late binding simply defines a common strategy we can use in developing ActiveX scripts: create an instance of the object model we’d like to exploit, use it, then close it and free the resources it is holding by setting the late bound object equal to nothing.

Here is a very brief DTS ActiveX script that demonstrates. Create a package on a test machine with a global variable name “Databases” with a data type of “<other>”, add an ActiveX script task, and paste this in the code box of the ActiveX Task’s properties, then execute the package interactively for a quick into to VB based ActiveX scripting.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function Main()

  Dim adoConnection

  Dim sConnect

  sConnect = "Provider='sqloledb';Data Source='(local)';Integrated Security='SSPI';Initial Catalog='master';"

  ' late binding, create the object

  Set adoConnection = CreateObject("ADODB.Connection")

  ' now use the late bound object

  adoConnection.Open sConnect

  RecordSetToGlobalVariable(adoConnection)

  RecordSetFromGlobalVariable

  ' done so clean up resources allocated to the late bound resource 

  adoConnection.Close

  Set adoConnection = Nothing

  Main = DTSTaskExecResult_Success

End Function

Private Sub RecordSetToGlobalVariable (Conn)

  Dim adoRecordset

  Dim sCommand 

  sCommand = "select name " & _

  "from master.dbo.sysdatabases " & _

  "where dbid > 4"

  set adoRecordset = CreateObject("ADODB.Recordset")

  adoRecordset = Conn.Execute (sCommand)

  ' put the recordset in an existing global variable

  Set DTSGlobalVariables("Databases").value = adoRecordset

End Sub

Private Sub RecordSetFromGlobalVariable

  dim RS

  set RS = CreateObject("ADODB.Recordset")

  ' get the recordset from a global variable

  set RS = DTSGlobalVariables("Databases").value

  MsgBox "You found the " & RS("name").value & " database. yee-haw!"

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here are some example – shown with late binding syntax - of common object models you might choose to make use of in your ActiveX scripts:

CreateObject("SQLDMO.SQLServer2")

The reason you would want to use a DTS ActiveX script include The ActiveX Script knows SQLDMO. The DMO object model is well described in SQL Server Books Online. You can add, modify, script, or remove just about anything on the SQL Server with SQLDMO. You can even execute stored procedures and SQL statements provided a rudimentary level of control over execution is adequate.

CreateObject("ADODB.Connection")

You can execute any valid T-SQL through an ADO connection, including DDL, DBCC, and configuration changes. You can even pass tables (well a one row record set with an open connection dangling somewhere anyway) to and from DTS ActiveX Tasks. Not so sure table passing is particularly useful since I don’t use the feature. In general the goal should be to keep your DTS packages and especially your ActiveX Script Tasks short and to the point. The advantage of DTS abstracting so much of the interface for us is reduced as the intra-task complexity increases.

CreateObject("Scripting.FileSystemObject")

You can access the file system using the standard and simple Windows Scripting Host FileSystemObject. You can create files and folders, see if they exist, delete them, even copy and edit them if necessary.

See the expandable bullets at the bottom of the meaty BOL articles, “Using ActiveX Scripts in DTS” and “Using ActiveX Scripts in a DTS Workflow” for some easy if not quite complete examples. For some useful examples of administrative tasks made easy and reliable through ActiveX scripts also consider the following scripts I have posted:

Free Space Monitoring Tool ActiveX Script Task from an earlier article, Free Space Monitoring Tool. Uses both a SQLDMO connection and an ADO connection so you can examine them side by side. DMO seems more conducive to administrative actions and ADO more amenable to logging activity.

Copy Jobs from Server A to Server B with cleanup

uses SQLDMO to remove all disabled jobs from a standby server and copy the enabled jobs from another server to that server. Run this DTS package from a scheduler once a night to make sure the days’ changes to production are propagated to the standby server.

Copy procedures from Database A to Database B

Uses SQLDMO to first remove the existing stored procedures from a database then copy all stored procedures to that database from a specified database on any accessible SQL Server. Primarily used in an asynchronous replication of stored procedures between two like databases such as when the data is replicated by transactional replication in a query server capacity.

Backup Primary Restore Warm spare (SQLLitespeed or native)

Uses SQLDMO.ExecuteWithResults to call an extended stored procedure for the SQL LiteSpeed backup or uses DMO to directly invoke the native backup and restore operations.

            Script DDL using DMO and Check Those Scripts in to SourceSafe

Performance Considerations

For the most part I would anticipate that what you might use DTS for in an administrative capacity would not be things that could unnecessarily stress the SQL Server. Still, be sure you test everything before you put it in a production environment. After all, it’s up to you as the DBA to set a good example. In the Books Online article , “Enhancing Performance of DTS Packages” appear these interesting facts about ActiveX Scripts and performance:

Depending on the type of transformation and your choice of scripting language, data pump operations that use Microsoft® ActiveX® transformation scripts can be up to two to four times slower than copy operations.

Using ordinal values to refer to columns in ActiveX transformations can be much faster then referring to columns by name. For example, use:

DTSSource(1) 

instead of:

DTSSource("CustomerID").

This speed improvement is not significant when the number of columns is less then 20, but can become significant when the transformation contains many columns. For fewer columns, ignore this optimization to make the script easier to read.

In addition, scripts written in Microsoft Visual Basic® Scripting Edition (VBScript) run approximately 10 percent faster than scripts written in Microsoft JScript®, which in turn run approximately 10 percent faster than scripts written in Perl.

If you haven’t been using ActiveX I heartily encourage you to get started with it as soon as possible. It’s a good step into the world of languages that the T-SQL (and DOS) centric DBA can use as she/he begins to prepare for the coming Common Language Runtime aware SQL Server versions. And it’s always good to have command of a flexible yet powerful “wrapper” as is DTS.

Bill