| 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:
- Less memory/CPU intensive than opening a browser.
- Timeouts are optional and can be set on a "per script" basis.
- No windows to close after every execution.
- 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.... |
