Usually we use an ASP page with Javascript or VBScript inside it to either get data from, or put data into SQL Server. However, from time to time you wish you could run that favorite, most useful ASP page that you’ve written that does all those great things and instead have something that happens in SQL Server cause the call to the page.
Maybe you need it to run on a scheduled basis, or in response to somebody doing something like filling out a form and having their data successfully updated in your SQL Server database. Maybe you’d like to create a trigger on a table for INSERT, UPDATE or DELETE that spawns an ASP page that does some other function you’ve learned to do, or write some files to the filesystem, or run that special ASP component you’ve got. The list can go on and on…
Well, fear not! It’s so easy to cause an ASP page to be triggered by SQL Server, you’ll probably laugh! And yet, very few developers have this powerful tool in their arsenal….
There are two simple tricks to making this work:
1. xp_cmdShell is a system stored procedure that spawns any executable file — vbs, bat, exe, com, wsc, you name it – and returns an optional output parameter value. You can put a call to this system sproc in a stored procedure of your own, call it from ADO script code, or even put it in a trigger. Here is the syntax, straight out of Books Online:
xp_cmdshell {‘command_string’} [, no_output]
Arguments’command_string’
Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not return any output to the client.
Return Code Values
0 (success) or 1 (failure)
2. In VBscript (and also in Jscript) we can use the Windows Shell object (Wscript.Shell) to run any file. If you do this:
Set WshShell =
Script.CreateObject(“WScript.Shell”)
WshShell.Run(“http://www.yahoo.com”)
— you are in business! Try it — put those two lines in a .vbs file, save it to your c:temp folder, and double click on the file. Presto! Up comes Yahoo in your browser. “Ah”, you say, “but what about passing querystring parameters into my ASP script?”
The Windows Script Host accepts command line arguments that can easily be translated into querystring parameters to control your ASP script using the Arguments.Named.Item( ) property:
exec xp_cmdshell ‘c:temptest.vbs /param1:abc /param2:xyz’
(be sure to use the syntax “/paramname:value” immediately following the invocation of the script name.) Now you need to pick up the two command line args we passed from the sproc call in SQL Server into the URL:
Set WshShell = WScript.CreateObject(“WScript.Shell”)
param1= WScript.Arguments.Named.Item(“param1”)
param2= WScript.Arguments.Named.Item(“param2”)
URL = “http://localhost/test.asp?param1=” ¶m1 & “param2=”& param2
WshShell.Run(URL)
set WshShell = Nothing
— and that’s it! Run any ASP file from SQL Server SQL, pass parameters to it, and let your ASP page take over.
Enjoy.
Dr. Peter Bromberg is a Senior Programmer/Analyst at Fiserv, Inc. in Orlando and a co-developer of the http://EggheadCafe.com developer
website.
