Periodically running asp page with timer

[Bill]

I have an asp page that will be updating the data from one database
into my sql server database every 3 hours. I'd like to write code that
will tell the page to automatically run every three hours. How do I do
this?

Thanks,

Bill

 

[Ray at]

Change to VBS and schedule with AT.
http://www.aspfaq.com/2143

Ray at home

 

[Chris Hohmann]

I have an asp page that will be updating the data from one database
into my sql server database every 3 hours. I'd like to write code that
will tell the page to automatically run every three hours. How do I do
this?

Thanks,

Bill

http://www.aspfaq.com/show.asp?id=2143

 

[Dave Anderson]

Change to VBS and schedule with AT.
http://www.aspfaq.com/2143

I have a similar need**, but I also want to give certain users the ability
to manually trigger the process. I wanted to avoid maintaining duplicate
scripts (one ASP script for end users, one JS script for scheduling), so I
chose one.

How would you handle this situation, Ray?

I chose ASP, primarily so I could provide feedback while the script runs,
since a human user expects to see that the script is doing something. This
bumped me right into the old "how do I schedule an ASP script" problem, and
I initially solved it by setting up a job in SQL Server (the one that was
receiving the updates).

The job used MSXML (specifically, MSXML2.ServerXMLHTTP.4.0) to kick off the
script, and it worked flawlessly until we moved the DB server from NT4 to
Win2K. I have since found that I cannot get a scheduled task to successfully
use the ServerXMLHTTP object on a Win2K machine.

I can create a VBS or JScript file and launch it successfully while logged
in, but that same script will not run as a scheduled task. Ditto for
compiled executables and (as noted previously) SQL Server jobs.

Any suggestions, Ray?


**In my case, I need to take a snapshot of part of a proprietary DB and
stick it into a SQL Server DB, which is the data store for an intranet
application. The vendor supplied us with a somewhat crude API as our only
means of talking to their DB, and it is not flexible to talk with SQL Server
directly.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Ray at]

You could still contain everything in a vbs file and then if you want a user
to be able to execute the script, use WshShell object to execute the vbs
file, if the user has permissions.

Ray at home

 

[Aaron Bertrand - MVP]

I have a similar need**, but I also want to give certain users the ability

to manually trigger the process. I wanted to avoid maintaining duplicate
scripts (one ASP script for end users, one JS script for scheduling), so I
chose one.

How would you handle this situation, Ray?

Have the VBS script run on a schedule, and in the cases where you need to
trigger it from ASP, use WSH to fire off cscript, or see
http://www.aspfaq.com/2059 (of course the ASP page will have to be reached
through authentication, or IUSR given sufficient privileges).

You could also call MSXML from a scheduled SQL Server job, and call
msdb..sp_start_job on manual occasions.

 

[Dave Anderson]

You could still contain everything in a vbs file and then if
you want a user to be able to execute the script, use WshShell
object to execute the vbs file, if the user has permissions.

As noted originally, this precludes the possibility of giving feedback to
the user other than an ambiguous "job started" message. This is a problem
for a couple of reasons.

As you may recall the script reads the vendor's DB via the vendor's API.
What I didn't mention is that I have to use this extraction method for
several sets of data in the vendor's DB, and the API cannot read from two of
them simultaneously. In fact, if an attempt is made to read from one set
while another is being read from, both processes are killed. This is the
primary reason we mirror the data on SQL Server, since it makes simultaneous
searches in multiple data sets impossible (secondary: speed of retrieval,
which is on the order of .1 second per record returned, tertiary:
readability of web scripts due to the horrible API).

This is why I like to present feedback to the web user -- I spit out a bit
of output every 100 records, so he can see how far the job has progressed.
He can see when the job finishes and can run the next one. I suppose I can
prevent two jobs from running concurrently, but I'm certain the API doesn't
allow me to check, so I'll have to flag the start/finish events in SQL
Server**.

I have actually considered using the third suggestion here:
http://www.aspfaq.com/show.asp?id=2143, since this is a very high-traffic
application. I will have to give it some consideration.



**I already track job events for reporting purposes, so this isn't a great
burden, but I'm hesitant to use such abstracted logic, since there are
obviously situations that could arise (if the web server or SQL Server box
crashed in the middle of the job, for example) that would cause the job to
abort without an appropriate flag in the DB. Job initiation logic requiring
the completion of already started jobs would then leave me in a state that
disallows *any* jobs from running until the DB entry is cleared.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Dave Anderson]

Have the VBS script run on a schedule, and in the cases where you
need to trigger it from ASP, use WSH to fire off cscript, or see
http://www.aspfaq.com/2059 (of course the ASP page will have to
be reached through authentication, or IUSR given sufficient
privileges).

I think my response to Ray addresses the above. However...

You could also call MSXML from a scheduled SQL Server job, and
call msdb..sp_start_job on manual occasions.

Tried that already. I keep getting "access denied" errors when attempting to
use MSXML in a job. If you have any insight there, I'd be grateful, since
I'd be home free. My jobs were set up long ago, and only stopped working
when the DB moved from an NT4 box to a Win2K one.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron Bertrand [MVP]]

Tried that already. I keep getting "access denied" errors when attempting
to

use MSXML in a job. If you have any insight there, I'd be grateful, since
I'd be home free. My jobs were set up long ago, and only stopped working
when the DB moved from an NT4 box to a Win2K one.

Well, who does SQL Server run as, local system account, a domain account,
....? Same question for SQL Server agent. This user must have permission to
instantiate the MSXML object, and I doubt a restricted user like local
system account has such permission (in fact this is why xp_cmdshell also
often fails when called from a job). If you set SQL Server Agent to log on
as a specific user, e.g. with local admin rights, then it should be able to
call COM objects no problem...