?
Solved

SQL Server Agent jobs and dependencies

Posted on 2004-09-03
14
Medium Priority
?
1,778 Views
Last Modified: 2008-03-03
I have 6 SQL Server Agent jobs that have to run every hour.  5 of them are jobs which update data from a Progress database using ODBC to a SQL Server stored on a network computer.  The 6th job updates the "updated" tables from the network computer to the SQL Server on the webserver.   The 5 jobs are timed to finish running by 4 after the hour.  The 6th and final job has to run by 6 after the hour.  Most of the time this runs fine.  But during the 1st - 5th of the month (Heavy traffic time) some jobs may fail.  Is there a way for the 6th job to NOT run if any of the previous 5 jobs have failed?
0
Comment
Question by:BSitko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11975123
Schedule them all under the same SQLAgent job....When you add a new step to the job, you can set dependencies based on the successful (or not successful) execution of the previous job/step.
0
 

Author Comment

by:BSitko
ID: 11975186
Could I still schedule each of the individual "jobs" to run at different times?  i.e. Job1 = 11:59 , Job2 = 12:00 , Job3 = 12:00:30 ...etc.  Running them all back-to-back would take too long.  (About 10 minutes)
0
 
LVL 34

Expert Comment

by:arbert
ID: 11975240
Not in the scenario I gave above....You could possibly build an "indicator" table that you have the job write a flag to saying it's done...Then, in the jobs with dependencies, have them check this table before they run.....
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:BSitko
ID: 11975340
Nothing programmatically can be done to tie in what's existing?  I somehow knew it wasn't going to be easy...
0
 
LVL 8

Accepted Solution

by:
SNilsson earned 750 total points
ID: 11975571

In your fifth job you can add the sixth job as a step to be run if the fifth was ok.

You can also do this in a DTS package and add decision ways depending on succes failure and in the order you want, so maybe you should check if using a dts package is something for you.

Post again if you have any questions about converting your current job's to dts.
0
 
LVL 9

Assisted Solution

by:SoftEng007
SoftEng007 earned 750 total points
ID: 11975758
run this query to check if a job failed:

exec [sp_help_jobhistory] @job_name='Name goes here',@Step_id =0 ,@run_status=0 --,@start_run_date='date here' --to filter to a single days run

a 0 run status for step 0 (job outcome) indicates the job failed.

you can use this in an if exists query and raise an error in it returns this will fail you 6 job and then it won't run.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11975773
SoftEng007, very good idea......
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11976510
Thanks,
I had to use this method to implement email notifications to user groups when certain batchjobs failed.
They had a very convoluted process in place to sync user jobs together.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 11976565

That is if job five have finished, if it's still running jobhistory wont have any 'success' data in it.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11976689
"They had a very convoluted process in place to sync user jobs together."

Ingenuity by necessity :)
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11976790
SNilsson,
you are quite correct.
i tried to answer
>>Is there a way for the 6th job to NOT run if any of the previous 5 jobs have failed?

There are other methods and sysobjects tables that can be queried if you need to know if a job has started AND finished.

you could for instance change the step_id to look for 1 which is the first step. if it's not there the job hasn't started.
OR
you could use a while loop and an not exist check against step_id=0
when you find it then run my query listed in my previous post to determine the outcome.
0
 

Author Comment

by:BSitko
ID: 11977748
Give me some time over the weekend to check these ideas and I'll get back to you.  (Sorry for the delayed response but I've been out of the office).  500 points will be worth the wait?  right?  
0
 

Author Comment

by:BSitko
ID: 12000499
SoftEng007 ... where would such code go?  Would it preceded job 6 running?  Where?  Forgive this type of question but I'm just not sure.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12000664
job 6 should have 2 steps.
The first step is the check step it should use the (if exists...) to check the job history.

use raise error if the row is not there.  for the job set this step to fail on error. goto next step on sucess.

step 2 should run your process.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question