Linking Scheduled Jobs

I currently have 4 scheduled jobs which are all scheduled for 4
different days and was wondering if there was anyway these could be be
stopped automatically in the event of a job failure, consider the
example below

Monday
Job runs to produce a file requesting BACS Payments.

Wednesday
Job runs to create a file that can be used to allocate the money
recieved from the bacs payment to a particular service.

If the job that runs on monday fails it would be nice for the job that
is due to run on wednesday to be automatically suspended but I can't
find anyway for this to be done.

Any help in this problem would be appreciated

Stuart FergusonStuart Ferguson wrote:
> I currently have 4 scheduled jobs which are all scheduled for 4
> different days and was wondering if there was anyway these could be be
> stopped automatically in the event of a job failure, consider the
> example below
> Monday
> Job runs to produce a file requesting BACS Payments.
> Wednesday
> Job runs to create a file that can be used to allocate the money
> recieved from the bacs payment to a particular service.
> If the job that runs on monday fails it would be nice for the job that
> is due to run on wednesday to be automatically suspended but I can't
> find anyway for this to be done.
> Any help in this problem would be appreciated
> Stuart Ferguson

I'm not sure if there is a way to do exactly what you want, but you
could do a workaround by creating a table that holds some value to
indicate a successful job. The first part of the Wed. job could be to
check the value of that table, if it isn't what it is supposed to be, it
can terminate.

For example:

CREATE TABLE Job_Status (Completed DATETIME NOT NULL)

Monday's Job:

TRUNCATE TABLE Job_Status
-- Run Monday's job
As last step of Monday's job:
INSERT INTO Job_Status (Completed) VALUES (CURRENT_TIMESTAMP)

Wednesday's Job:

IF EXISTS (SELECT * FROM Job_Status)
-- Do Job

If you wanted to get fancy you could make the code more complex to check
for job history and keep a record of when jobs fail or not, etc.

Zach|||Stuart Ferguson (stuart_ferguson1@.btinternet.com) writes:
> I currently have 4 scheduled jobs which are all scheduled for 4
> different days and was wondering if there was anyway these could be be
> stopped automatically in the event of a job failure, consider the
> example below
> Monday
> Job runs to produce a file requesting BACS Payments.
> Wednesday
> Job runs to create a file that can be used to allocate the money
> recieved from the bacs payment to a particular service.
> If the job that runs on monday fails it would be nice for the job that
> is due to run on wednesday to be automatically suspended but I can't
> find anyway for this to be done.

As Zach says, you would somehow implement some sort of your own buisness
logic.

One way, would have two extra steps in the Monday job. When step 1 succeeds,
the job should proceed to step 2 which enable the job for Wednesday. When
the step fails, the should proceed to step 3 to disable the Wednesday.

To enable/disable jobs, you would have to use the job-control stored
procedures that are described in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp