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:
Set WshShell =CreateObject("WScript.Shell")
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
Peter Bromberg is an independent consultant specializing in distributed .NET solutions
Inc. in Orlando and a co-developer of the EggheadCafe.com
developer website. He can be reached at firstname.lastname@example.org