In most instances, any changes to data exist within the application context. Applications respond to events through interactions within the application system. However, unless your application exists within a system, events generated within a system are usually invisible to an external application.
Binding an external application to a system to consume these events usually involves a lot of good old-fashioned elbow grease and some luck. In today's disconnected systems, more and more applications need to react to events of a system but remain external to the system generating the events. Since most systems are responsible for manipulating data, changes to the data exist within the database. Triggers usually capture these changes and react by doing some action on the database; however, it would be nice to extend these events to the outside world. Within Microsoft SQL Server 2000, you can make use of the Web and Data Transformation Services (DTS) packages to extend these database events to external applications.
In this article, I'll use a stored procedure to execute a DTS package that will, in turn, run ActiveX script to send a request to a Web service that will coordinate the handling of the generated event.
Creating DTS packages on SQL Server 2000
When designing a system, you normally would want to consider all possibilities for connecting external systems to your own. However, you could end up in the push/pull battle of how to handle events: Do you poll the service to query the state, or do you generate an event that you hope the external system will be able to absorb? If your application is a business application that's responsible for manipulating and moving data, it would be nice to leave event creation to the database where data changes occur. These changes can be realized through table triggers, and new events can then be published to subscribers of events.
In SQL Server 2000, you can create a DTS package that will execute an ActiveX script. The ActiveX script is simply a VBScript file with some special knowledge of the DTS being executed. This special knowledge includes global variables that can be used to do conditional operations based on the information stored to those variables. Using the CreateObject statement, the script can create an instance of the ubiquitous MSXML2.ServerXMLHTTP component, which provides the ability to generate HTTP requests. In this light, the DTS package can make requests to Web services or even simple Web pages to facilitate an event. The service or Web application can then handle the request by performing necessary actions to handle this "event".
Now you need a method to facilitate executing the DTS package from within T-SQL. To encapsulate this functionality, a stored procedure would be an ideal candidate. Within the stored procedure, you can use the dtsrun executable to run a DTS package. Using the xp_cmdshell system stored procedure, you can run the dtsrun executable much like executing the program from the command line. There are a few command-line switches that you will need to execute the dtsrun EXE successfully. I'll use the following switches:
- /S—identifies the server name of SQL instance.
- /E—enables trusted security using the current context of the logged in user.
- /N—the name of the DTS package to execute.
- /M—the DTS execution password (if set).
- /A—the list of global variable names, types, and values passed to the DTS package.
Your stored procedure should execute the DTS package by executing the xp_cmdshell system stored procedure taking the command line string as a parameter. To build this string, your stored procedure should be able to accept the variable information as parameters. Staying true to form, the stored procedure to call the shell command accepts the server name, the name of the DTS package, the DTS password, and the global variables. The global variables should be passed as "VariableName":"datatypeid"="value". So an example of a DTS call from the stored procedure should look like this:
dtsrun /S (local) /E /N MyPackage /M dtspassword /A "variable1":"8"="some
The procedure to build this command line string is in Listing A. This effectively creates the command-line string, including the global variables depending on whether the @GlobalVariableList value is set. It then executes the dtsrun EXE with the command-line parameters through the xp_cmdshell system stored procedure. Finally, it returns an error code, if any.
Executing a DTS package on SQL Server 2000
Now that you can execute a DTS package, you'll need a DTS package to execute. The easiest way to build your DTS package is within the DTS package designer in SQL Enterprise Manager. To do so, follow these steps:
- Create a new package using the designer.
- Add a global variable called eventId as an Integer and set its value to 0.
- Add an ActiveX Script Task and add the following code to the script:
Dim oXmlHttp As Object
Set oXmlHttp = CreateObject("MSXML2.ServerXMLHTTP.3.0");
oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form
oXmlHttp.send "eventId=" & DTSGlobalVariables("eventId").Value
Set oXmlHttp = Nothing
Main = DTSTaskExecResult_Success
Building the Web service to absorb the generated event
You can add subscribers to the event generation service to a table in your database. Then the DtsRunTrusted stored procedure can collect the URLs of the subscribed event consumers, pass the URL to the DTS package as a global variable, and send an HTTP request to that subscriber.
In my example, I only intend to have one event consumer. But for multiple subscribers, it might be wise to set the asynchronous operation of the ServerXMLHTTP object to True. This way you're not held up by slow Web services, and events can be generated in a timely fashion.
Consuming database events using an ASP.NET Web service
It's simple to consume this database event. The event ID can relate back to a table of events that has more specific information regarding the system. Also keep in mind that, the information stored in the event table should be informative and abstract enough that any external application will be able to use the information in the system.
Listing B contains the skeleton code of an ASP.NET Web service that will handle the event. It's important to note that the NewEvent method is defined as a WebMethod, and it accepts one parameter: the eventId. Inside this method you should add the code to handle the generated event.
Now you can create data-centric applications and add process modules on at will. Any changes you make to the data will create event notifications to curious applications. Then those applications can handle those data changes as needed.
Miss a column?
Check out the Web Development Zone archive, and catch up on the most recent editions of Phillip Perkins' column.