Recently I was posed with an unusual, although not uncommon problem: I needed to run a SQL Server batch job that ran a set of SQL Scripts that were parsed and fired off by a COM component. Basically what the component does is receive an XML document as a method input parameter, parse it apart, and use the resulting "pieces" as input parameters to several SQL scripts that it calls in succession.

No big deal, right? Well the problem occurs because now the setup for the client is such that the machine running the COM component that does this is behind a firewall. in fact, the only way I can instantiate this component is to run an ASP page on the target machine (which fortunately has IIS running on it).

What we want to do in situations like this is run our target script or component from a VBScript ("ActiveX Script") scheduled batch job in SQL Server Agent. Here's how I "fixed" the problem:

1) First, Open up Enterprise Manager and go to the Management Folder for your database:

      EnterPrise Manager Showing SQL Server Agent Jobs Section

2) Next, Right click on "Jobs" and select "New Job". You'll get the following dialog:

3) You need to fill in a job name, owner etc. then select the "Steps" tab to enter your first job "Step":

4) You'll select "Visual Basic Script" and enter the following script that fires off the ASP page
on your remote server:

Dim WshShell
Set WshShell =CreateObject("WScript.Shell")
WshShell.Run ("http://yourserver/yourfolder/YourASPPage.asp?yourparameter=<YourXMLDocumentRoot><Yourtag>Whatever</YourTag></YourXMLDocumentRoot>")
Set WsShell = Nothing

The above script executes a web page from the Windows Script Host Shell object "Run" method.

5) Last but not least, you probably need to schedule your batch job from the Schedule Tab:

And that's pretty much all it takes to have SQL Server run a scheduled batch job that executes a remote ASP page on a remote server on another domain through a firewall, that instantiates a COM component that does whatever you need to do with the parameter(s) you pass in on the querystring!

Peter Bromberg is an independent consultant specializing in distributed .NET solutions Inc. in Orlando and a co-developer of the NullSkull.com developer website. He can be reached at info@eggheadcafe.com