Delaying scheduled jobs on condition

[drlex]

Hi there. Not sure whether this forum or the DTS forum (forum961) would be best for this q, but I'll post here.

I usually work with Cognos software, but have been constructing a small datamart with SQL server 2000 to stage transaction data in. The timetable is that from 3 a.m. to 8 a.m., I have a schedule of around 4 dozen DTS jobs that refresh the SQL server data from the transactional database, prior to the Cognos jobs starting at 8.

I have an initial job which detects whether the transactional database is back on line. If it's not, it shuts down SQL server and sets a flag so that the Cognos jobs do not run (separate Scheduler). It's crude, but was a fail-safe solution on my limited SQLserver skillbase

Recently, there have been frequent occasions when the transactional DB has not been available (disk problem/backup/incorrect Unix scripts) and the SQL server has been correctly shutdown. I therefore have ended up manually starting each DTS job prior to restarting my Cognos Schedule (which helpfully will run jobs that have not been run according to schedule, so can be "kicked off" and left to catch up).

Am I right in thinking that there is no way to 're-trigger' scheduled jobs that have not been run within a set window?

Or am I missing an easy way around this?

Currently, I am thinking that I should have a table of jobs and use the sp_{update/add/delete}_jobschedule command to either amend or construct afresh the daily schedule on the result of the DB test, and a success log table to ensure that the daily routines are run only once a day.

All assistance/advice/views welcome; thanks.

lex





soi la, soi carré

 

[drlex]

Well, found some code to step through rows in a table, so used that to loop through the sysjobsschedule table to kick forward all early jobs by ten minutes.

Logic is.
1. Copy sysjobschedule table for all early jobs scheduled for today.
2. DTS job to check ODBC connection and report failure if down before looping through sysjobschedule to update runtime of selected jobs by 10 minutes hence.
3. At designated time, use the copied table to revert job times back to the normal run times and set date to run as next day/week/month as appropriate.

Each step is performed by a DTS job. I guess one ought to use SQL-DMO to do everything in one step, but I'm not a natural SQL expert. Frustrations were the way in which the braces weren't transferred in reading the job_ids and the formatting of dates and times in the sysjobschedule table as integers.



soi la, soi carré