June 2, 2004 at 12:40 pm
I have the need to execute a stored procedure every 5 seconds on my database. The ultimate solution to me would be to have this run as a SQL Agent job. However, the dialog for defining a schedule doesn't appear to accept fractional minutes, nor does it show "seconds" as a timeframe in the dropdown. Is there some other way to configure this, or am I going to be stuck doing this through a windows service application?
Thanks in advance!
Ross Environmental Services
June 2, 2004 at 1:08 pm
You could set the stored proc to loop and use a WAITFOR DELAY 00:00:05 before the loop completes. Then call the proc every time SQL starts.
June 2, 2004 at 2:01 pm
That's a good idea, thanks. Any idea if I kick it off when SQL starts (which won't be often, since this is in a 24/7 environment), will I have any notification if for some reason the SP fails, like the "page on failure" option on the job? This function is a monitoring function so to speak, and I'll need to know ASAP if it falls over.
I see I forgot to mention, that this is in a SQLserver 7.0 envionrment.
Ross Environmental Services
June 3, 2004 at 5:30 am
As the job will run continuously (as the sp will never stop running unless it fails) setting the notification option on job failure will alert you if you get any problems.
June 3, 2004 at 6:28 am
Nicholas,
Thanks a bunch for your help. Based on your input, I put together a proof of concept in our development enviornment, even to the point of dropping a table out from under the stored procedure to force it to fail, and everything works exactly as we need it to. With a some well thought out final design, I'm sure this is going to be the clean and reliable solution I was looking for.
Thanks again!
Ross Environmental Services
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply