article

Automating Tasks with WSH

Email
Submitted on: 1/5/2015 7:08:00 AM
By: Mark Lidstone (from psc cd)  
Level: Intermediate
User Rating: By 11 Users
Compatibility: ASP (Active Server Pages), VbScript (browser/client side)
Views: 1983
 
     The purpose of this article is to show how to run tasks at regular intervals without having to access an ASP script with your web browser manually.

 
				

Automating Tasks

A common question with ASP is "How do I run a certain page at regular intervals?". Normally people want to do this because they have a page that performs all their database maintenance or does something like send of reminder emails. Windows NT has a method of scheduling tasks to run at a specific time, but it only allows command-line tasks to be run. To use this all you have to do is use the "AT" command at the console and leave the "Scheduler" service running.

It is possible to start, for instance, Internet Explorer from the command line and tell it to request your page (e.g. "c:\program files\internet explorer\iexplore.exe http://localhost/mypage.asp"). Netscape also gives this ability, but using either will mean that every time the scheduled task runs you will be opening a new browser window and will need to get to the server and actually close the browser window. Running a browser also has a pretty high overhead in terms of disk access, memory space/bandwidth and processor time, and if you went on holiday for a month and the task was run every day you'd come back to a server with about 30 open browser windows. Not nice!

Another drawback is that scripts running through ASP.DLL can timeout. If you are doing a lot of work and you know that the task is going to take longer than your default timeout value, it can make things a little more complicated getting them to run. Although it's possible to make the timeout longer, that means that malformed scripts on other parts of your site can take up more processing time, and changing the timeout for individual pages only allows you to reduce the timeout delay, not lengthen it.

Luckily it is possible to run scripts from the command-line directly, without requesting ASP scripts through the web server thanks to the Windows Scripting Host (WSH). For a rather dry overview of what WSH is, you can see the article at http://www.microsoft.com/MANAGEMENT/ScrptHost.htm

The main advantages to using WSH instead of an ASP script are:

  1. Less memory/CPU intensive than opening a browser.
  2. Timeouts are optional and can be set on a "per script" basis.
  3. No windows to close after every execution.
  4. Simpler code production.

Writing WSH scripts is not difficult at all. Normally you can convert your ASP scripts to WSH scripts in a matter of seconds, and to show you what I mean, I'll convert an example ASP script to a WSH script. The source below is for a page that removes all entries in the "tblNewsItems" that are over a week out of date and displays a list of the articles that have been deleted.

<html>
<head><title>Database Maintenance Page</title>
<body background="#FFFFFF">
<h1>Database Maintenance</h1>
<!-- #include virtual="/includes/adovbs.inc" --><%

' Define variables Dim objConn, objRS Dim dtmCutoffDate Dim strCutoffDate
' Make sure the date format cannot be confused (I'm paranoid about this because I'm British) dtmCutoffDate = DateAdd("d",-7,Date) strCutoffDate = Day(dtmCutoffDate) & " " & MonthName(Month(dtmCutoffDate)) & _ " " & Year(dtmCutoffDate)
' Create and setup connection object Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "MyDSN"
' Retrieve records that are to be deleted Set objRS = Server.CreateObject("ADODB.RecordSet") objRS.Open "SELECT * FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & _ "#;", objConn, adOpenKeyset, adLockOptimistic, adCmdText
' If there are some articles returned, print their details then remove them from ' the database If NOT objRS.EOF Then Response.Write "The following articles were out of date and have " & _ been deleted :" & vbCrLf Response.Write "<table border=0 cellpadding=1 cellspacing=1>" & vbCrLf Response.Write vbTab & "<tr><th>Article Title</th><th>Author</th>" & _ <th>Start Date</th><th>End Date</th></tr>" & vbCrLf While NOT objRS.EOF Response.Write vbTab & "<tr><td>" & objRS("strTitle") & "</td>" & _ "<td>" & objRS("strAuthor") & "</td><td>" & _ objRS("dtmStartDate") & "</td><td>" & objRS("dtmExpireDate") & _ "</td></tr>" & vbCrLf objRS.MoveNext WEnd Response.Write "</table>" & vbCrLf objConn.Execute("DELETE FROM tblNewsItems WHERE dtmExpireDate < #" & _ strCutoffDate & "#;") Else
' If no out of date articles were found, explain and carry on Response.Write "No out of date articles were found" & vbCrLf End If
' Tidy up objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing
%></body> </html>

The main difference is that you don't need to put anything inside script delimiters like "<% .... %>" or "<script runat=Server> .... </script>" because the entire file is treated as script. Also, there are no "Request" or "Response" objects because there will be no input or output from IIS. Changing our script to take account of this (and deleting everything outside the delimiters) gives us this :

' Define variables
Dim objConn, objRS
Dim dtmCutoffDate
Dim strCutoffDate

' Make sure the date format cannot be confused (I'm paranoid about this because I'm British) dtmCutoffDate = DateAdd("d",-7,Date) strCutoffDate = Day(dtmCutoffDate) & " " & MonthName(Month(dtmCutoffDate)) & _ " " & Year(dtmCutoffDate)
' Create and setup connection object Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "MyDSN"
' Retrieve records that are to be deleted Set objRS = Server.CreateObject("ADODB.RecordSet") objRS.Open "SELECT * FROM tblNewsItems WHERE dtmExpireDate < #" & _ strCutoffDate & "#;", objConn, adOpenKeyset, adLockOptimistic, adCmdText
' If there are some articles returned, print their details then remove them from the database If NOT objRS.EOF Then While NOT objRS.EOF objRS.MoveNext WEnd objConn.Execute("DELETE FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & "#;") Else
' If no out of date articles were found, explain and carry on End If
' Tidy up objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing

We now have the problem that adovbs.inc is not included, so as a work-around you can open up the adovbs.inc file (or whatever include files you are working with) and copy the relevant lines into your code (luckily adovbs.inc only includes constant definitions and I only need two of them. When using include files that contain large amounts of code this can make the script difficult to navigate) :

' Define variables
Dim objConn, objRS
Dim dtmCutoffDate
Dim strCutoffDate

' Define constants from ADOVBS.INC Const adOpenKeyset = 1 Const adLockOptimistic = 3 Const adCmdText = &H0001
' Make sure the date format cannot be confused (I'm paranoid about this because I'm British) dtmCutoffDate = DateAdd("d",-7,Date) strCutoffDate = Day(dtmCutoffDate) & " " & MonthName(Month(dtmCutoffDate)) & " " & _ Year(dtmCutoffDate)
' Create and setup connection object Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "MyDSN"
' Retrieve records that are to be deleted Set objRS = Server.CreateObject("ADODB.RecordSet") objRS.Open "SELECT * FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & _ "#;", objConn, adOpenKeyset, adLockOptimistic, adCmdText
' If there are some articles returned, print their details then remove them from the database If NOT objRS.EOF Then While NOT objRS.EOF objRS.MoveNext WEnd objConn.Execute("DELETE FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & "#;") Else
' If no out of date articles were found, explain and carry on End If
' Tidy up objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing

In this example we can now see that the object "objRS" is redundant, it's whole point being for display of the data. Taking out all references to that object gives :

' Define variables
Dim objConn
Dim dtmCutoffDate
Dim strCutoffDate

' Make sure the date format cannot be confused (I'm paranoid about this because I'm British) dtmCutoffDate = DateAdd("d",-7,Date) strCutoffDate = Day(dtmCutoffDate) & " " & MonthName(Month(dtmCutoffDate)) & " " & _ Year(dtmCutoffDate)
' Create and setup connection object Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "MyDSN"
' Run the SQL query objConn.Execute("DELETE FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & "#;")
' Tidy up objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing

This can be tidied up even further like so :

' Define variables
Dim objConn
Dim dtmCutOffDate

' Make sure the date format cannot be confused (I'm paranoid about this because I'm British) dtmCutoffDate = DateAdd("d",-7,Date)
' Create and setup connection object Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "MyDSN"
' Run the SQL query objConn.Execute("DELETE FROM tblNewsItems WHERE dtmExpireDate < #" & Day(dtmCutoffDate) & _ " " & MonthName(Month(dtmCutoffDate)) & " " & Year(dtmCutoffDate) & "#;")
' Tidy up objects objConn.Close Set objConn = Nothing

The script has now been stripped down to the bare basics with nothing except the real bones functionality of the original. This is much cleaner to look at and will be more efficient.

Out of interest, it is also possible to remove references to the connection object instead of references to the recordset object like so :

' Define variables
Dim objRS

' Define constants Const adOpenKeyset = 1 Const adLockOptimistic = 3 Const adCmdText = &H0001 Const adAffectAll = 3
' Create and setup recordset object objRS.Open "SELECT * FROM tblNewsItems WHERE dtmExpireDate < #" & strCutoffDate & _ "#;", "MyDSN", adOpenKeyset, adLockOptimistic, adCmdText
' Remove out of date articles from the database objRS.Delete adAffectAll objRS.Update
' Tidy up objects objRS.Close Set objRS = Nothing

but this method is slightly less efficient because it returns the matching records before deleting them from the database whereas using the connection object deletes the entries directly from the database without loading them into memory first.

Alternatively, you may find it easier to just rewrite your code from scratch. This way you shouldn't end up accidentally including some code that was meant for formatting output which isn't needed any more.

Now that you have a script like this you need to save it with the ".vbs" extension. If you now look at the script in an Explorer window it shouw have an icon like a small scroll of blue paper. Double-clicking it actually runs the script and performs the same funcion as the ASP page on your website, but without the need to access it with a browser.

If you go to the command-line and try typing in the name of the file, you will get the standard "I don't know what to do with this file" message that you get from the console, so what you need to do is tell it that you want to run the WSH and pass it the script.

There are two ways to call the WSH engine from the command-line, which are "CSCRIPT" which calls the command-line version of the WSH, and "WSCRIPT" which calls the windows version. As the command-line version seems to have a lower overhead I'll stick to that one. Let's assume that your script is saved as "dbmaintain.vbs" in the "c:\scripts\" directory. The console command to run that script would be "cscript c:\scripts\dbmaintain.vbs". You can pass this command directly to the AT scheduller, or you can place it in a batch file and pass the batch file to AT. Voila! You now have a working maintenance script.

Here's a quick tip. If you have a server that has several tasks that need to be run at regular intervals you might find it easier to create a set of batch files representing different time-plans or repetition frequencies. e.g. you could have a batch file called "hour.bat" which is run every hour, one called "day.bat" which is run every day etc... This means you don't have to re-type the long AT commands every time you want to add or change a task, and it makes looking up what tasks are run at what frequency much easier.

Of course, database maintenance is not the only thing that this can be useful for. Several people have asked if it is possible to do something like email a client x number of days before an advertisement they have placed expires. This again is simple and just needs the SQL statement to be changed to something like:

strSQL = "SELECT strPlacerName, strPlacerEmail, dtmExpireDate FROM " & _
	"tblAdverts WHERE dtmExpireDate >= #" & DateAdd("d",-5,Date) & "#;"

Which would return a recordset populated with all the adverts that are due to expire in the next 5 days. Emailing all of these people then would be a simple matter using CDONTS or some other mailer component. You could then improve the system to query the database for anyone whose advert expires in 5 days and give them a reminder, then look for ads that expire in 2 days and give them a more urgent message etc....


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.