To read the article online, visit http://www.4GuysFromRolla.com/webtech/101399-1.shtml

Automating Tasks with WSH

By Mark Lidstone


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 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.

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. You can download the latest version of the Windows Scripting Host at http://msdn.microsoft.com/scripting.

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 = CreateObject("ADODB.Connection")
	objConn.Open "MyDSN"

	' Retrieve records that are to be deleted
	Set objRS = 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 = CreateObject("ADODB.Connection")
	objConn.Open "MyDSN"

	' Retrieve records that are to be deleted
	Set objRS = 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 = CreateObject("ADODB.Connection") objConn.Open "MyDSN" ' Retrieve records that are to be deleted Set objRS = 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 dtmCutoffDate = DateAdd("d",-7,Date) strCutoffDate = Day(dtmCutoffDate) & " " & MonthName(Month(dtmCutoffDate)) & _ " " & Year(dtmCutoffDate) ' Create and setup connection object Set objConn = 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 : <P> <TABLE WIDTH=95% BORDER=0> <TR><TD WIDTH=100% BGCOLOR=#CCCCCC> <CODE><XMP> ' Define variables Dim objConn ' Create and setup connection object Set objConn = CreateObject("ADODB.Connection") objConn.Open "MyDSN" ' Remove out of date articles from the database 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 ' 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 should have an icon like a small scroll of blue paper. Double-clicking it actually runs the script and performs the same function 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 scheduler, 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....

Happy Programming!

  • By Mark Lidstone


  • Article Information
    Article Title: Automating Tasks with WSH
    Article Author: Mark Lidstone
    Published Date: Wednesday, October 13, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/101399-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers