scheduled asp script

simonneaves · August 18th, 2005, 05:47 AM

Hi,

Is is possible to run an asp sever script according to a schedule rather than being invoked by a browser request?

Here's why (any alternative suggestions welcome):
I am building an ecommerce site where the product supplier provides access to a text document containing all currently out of stock items. The text document is updated hourly. I want to be able to run a script every hour which updates a column in my product database so that none of the products with 0 in the stock column are returned in my queries. Because of the paging of products, it would be better if products are not in the recordsets when querying the database (rather than trying to remove or skip records after the database has been queried).

I can simply invoke the script every time somebody visits the homepage for example, but I'd rather not put this burdon on the server when the user will be waiting for the page to be served. It would be better if the database is continuously updated every hour without the need for interaction from a site user.

Any thoughts?

Smokie · August 18th, 2005, 08:05 AM

The burdon is going to have to be on something! I have in the past created an .vbs file which launches internet explorer on the server and goes to a specific page, this page performs my database tasks. The vbs file is set to run everyday using Windows built-in task scheduler. I think that would be a solution for you, heres the vbs code:

Code:

Set newShell = CreateObject("WScript.Shell")
newShell.run "http://my-site.com/task.asp",5,false

Hope that helps

simonneaves · August 18th, 2005, 08:44 AM

ok, but my site is hosted on shared hosting space, won't this prohibit me running processes such as this? I'm afraid I'm a newby as regards vbs applications and client side scripts. My asp knowledge only stretches into its implementation on web pages as server side scripting.

I've also just discoved that I have a problem reading the .csv file on the product suppliers site using the asp fso object. I think it is something to do with permisions at their end, but I can't speak to anybody there until monday.

All a bit belly up really, but thanks.

Smokie · August 18th, 2005, 12:05 PM

in that case the .vbs and task scheduler will have to run on your computer or a server that you have full control over.

simonneaves · August 18th, 2005, 01:12 PM

hmm. right. I'll have a think.

Thanks for all your help.

Webforumz Staff · August 18th, 2005, 02:09 PM

Smokie's suggestion is the usual way this is done.

Rob · August 18th, 2005, 09:09 PM

Let me make an alternaive suggestion....

The text file (containing out of date stock items) should be placed in a directory and queried everytime a product SELECTtion (from the database) is performed.

Now whilst that may sound pretty slow we are actually only talking about milliseconds here so the 'slow' will likely be mostly unnoticeable.

My suggestion would be to read the text document: perform the needed action to mark products out of stock in the DB and then... delete the text file.

Every time a product SELECTion is needed, just see if that file exists, if it doesnt, do nothing.... if it does, then do yer stuff.

Just a suggestion.

Webforumz Staff · August 19th, 2005, 04:11 AM

Doesn't windows hosting have anything similiar to Unix's cron?

Smokie · August 19th, 2005, 08:22 AM

the closest thing to cron is the windows task scheduler and typically microsoft didnt make it web friendly on their webserver products... Some webhosts do offer asp scheduling (crystaltech) but its pretty rare AFAIK. Robs sugesstion is the cleanest.

Rob · August 19th, 2005, 02:29 PM

Quote:

Originally Posted by Smokie

Robs sugesstion is the cleanest.

Well.... I thought so too

JohnMitch · August 23rd, 2005, 02:03 AM

Hate to not be offering help... but this is VERY usefull information to me, I just stumbled across this same problem. Thanks guys

simonneaves · August 24th, 2005, 07:40 AM

Sorry about the delay in replying. I've been tring to get hold of the company which holds the text file on their server.

I agree with everything you guys have said (thanks), and will run some script to update the database at those times when a SELECTion is made (as Rob suggested), rather than trying to schedule the script.

The problem at the moment is that the text file containing the info is on the suppliers server, and I haven't managed to read from it using my asp fso object. I just get a "file cannot be found" message. I gather that reading remote files often fails because of permissions on the source file. I am waiting to hear back from the technical guys at the supplier about this.

If anybody has anymore pointers about reading remote text files using the fso, or if there is a method by which I can automatically copy the remote file onto my server so that I can read it from there, I would be grateful.

Thanks again to all!

Smokie · August 24th, 2005, 07:49 AM

i think you'll need to use Microsoft's ServerXMLHTTP component (should already be available on your server) to grab the text file, this example should get you started:

Code:

<%
Dim objXmlHttp
Dim strTextFile

Set objXmlHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")

objXmlHttp.open "GET", "http://server/text.txt", False

objXmlHttp.send

strTextFile = objXmlHttp.responseText

Response.Write(strTextfile)

Set objXmlHttp = Nothing
%>

...oh and, no you wont be able to read a remote text file using the FSO.

simonneaves · August 24th, 2005, 08:02 AM

ah right...
Cheers man. I'll have a go with this.

simonneaves · August 24th, 2005, 08:22 AM

wow! brilliant. This looks to be just what I need. Cheers smokie.

simonneaves · August 24th, 2005, 10:46 AM

Thanks guys, all sorted now.
I've ended up with a script which sets an application variable which keeps track of when the last update was performed. The overall script is run everytime somebody queries the database, but the update based on the remote text file is only performed in my database if the application variable was set more than an hour ago. This means that I can guarantee that the database is never more than one hour out of date, but that the script isn't run more than once an hour.

Heres my final script for anybody who's interested

Code:

<%
updateDifference = DateDiff("s",now(),application("lastupdate"))	
updateDifference=cint(right(updateDifference,len(updateDifference)-1))
if updateDifference>3600 then 
	
	Set objXmlHttp = Server.CreateObject("Msxml2.ServerXMLHTTP") 
	objXmlHttp.open "GET", "http://www.remoteserver.com/textfile.csv", False 
	objXmlHttp.send 
	strTextFile = objXmlHttp.responseText 

	sql= "UPDATE products SET stock='n' WHERE supplier='apollo'"
	objconn.execute(sql)

	codes = split(strTextfile, ","&chr(13)&chr(10))
	i=0
	for i = 0 to Ubound(codes)
		sql= "UPDATE products SET apollostock='y' WHERE product_id='"&codes(i)&"'"
		objconn.execute(sql)
		response.write codes(i)
	next
	application("lastupdate")=now()
	Set objXmlHttp = Nothing 
end if
%>

Smokie · August 24th, 2005, 11:48 AM

Great job, glad it worked for ya!