January 23, 2013 at 6:47 am
I have a sql Job B dependant on Job A. Job B should run only if the job B completed successfully. How can I add a step in Job B to check if the Job A ran and completed successfully? And if not then check it again after some time the status of the job until the job A succeeds.
January 23, 2013 at 7:08 am
Why not merge the step(s) in job B into job A? If the original steps in job A fails, the you quit the job, otherwise you continue to the steps from job B.
January 23, 2013 at 8:31 am
I can't have both jobs running togather. Job doesnt need to run every time the Job A runs. Also Job A completes on different times but Job B should run on a sprcific time when it sees the last run ststus of job A completed successfully.
January 23, 2013 at 8:59 am
selectj.name
, convert(smalldatetime, convert(varchar(20), max(jh.run_date)))
, CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS Run_Status
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobhistory jh on
j.job_id = jh.job_id
where j.name = 'your job name'
group by j.name, jh.run_status
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2013 at 10:48 am
That's trickier than it sounds, because of the way SQL Server updates the sysjob* tables, and because you don't want to have to hardcode job schedules into the code.
Instead of relying on sysjob* tables, I'd suggest something along these lines:
the first action "Job A" takes is to update a row in a control table with a current datetime and a status that means "Job A has started but it has not fininshed yet";
the last action "Job A" takes is to set that control status to "Job A has finished successfully.";
if "Job A" fails it set that status accordingly.
Then "Job B" evaluates the "Job A" status:
A) if "Job A" was successful since "Job B" last ran, "Job B" can update its own separate datetime and status and run normally.
B) if "Job A" failed or didn't run,
"Job B" can update its datetime and/or status and exit (or whatever you want to do)
C) if "Job A" is still running, "Job B" can issue a WAITFOR and try again later; add a counter to limit the number of tries
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 23, 2013 at 11:01 am
and all that's easier than a quick check in the dbo.sysjobhistory table??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2013 at 11:14 am
Since you want to check the current status of the job, I'd use sysjobactivity. Perhaps something like this?
WHILE (
(SELECT TOP 1 ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = 'UID'
ORDER BY run_requested_date DESC ) = 0)
BEGIN
WAITFOR DELAY '00:05:00' -->> Recheck after a specified period of time
END
--> Run what should run if the job above isn't currently executing
PRINT 'Good to go!'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 23, 2013 at 11:25 am
Perry Whittle (1/23/2013)
and all that's easier than a quick check in the dbo.sysjobhistory table??
Yes: which specific entry for "Job A" does one check in sysjobhistory?
The last one? What if that's from the previous run of "job a", and "job a" has not run again since "job b" last ran?
I think it will be easier to quickly determine the relative status of job "a" vs job "b" with specific flags for each, rather than relying on assumptions about each job.
I guess I'm willing to assume, as you clearly must, that msdb.dbo.sysjobhistory is not accidentally cleared between "a" and "b"; although it may be more possible than you think, depending on the number of job failures/retries, delays, and the (default) "remove agent history" settings of the SQL Server Agent.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 23, 2013 at 3:22 pm
You could also have the last step in the job add a message in a queue for the service broker to pick up and execute the next job.
Or what i have done is have self maintaining schedules for a job. If it was sucessful it recesudules itself monthly (if it is not already on the monthly schedule). If it is not sucessful it changes its schedule to be weekly to try next week (there is a lot more decisions and complexity to it). This can also be translated into the idea of having your schedule for Job B disabled. At the last step of Job A it enables the schedule for Job B and then at the last step (probably to be reached regardless of sucess or fail) you disable the schedule again for Job B.
There are many different types/ways you can accomplish this from adding new schedules to on the fly create a new one time, delete after run job that is created by the sucessfull job A to be executed at a specific time/date.
January 23, 2013 at 3:41 pm
DBA_Dom (1/23/2013)
You could also have the last step in the job add a message in a queue for the service broker to pick up and execute the next job.Or what i have done is have self maintaining schedules for a job. If it was sucessful it recesudules itself monthly (if it is not already on the monthly schedule). If it is not sucessful it changes its schedule to be weekly to try next week (there is a lot more decisions and complexity to it). This can also be translated into the idea of having your schedule for Job B disabled. At the last step of Job A it enables the schedule for Job B and then at the last step (probably to be reached regardless of sucess or fail) you disable the schedule again for Job B.
There are many different types/ways you can accomplish this from adding new schedules to on the fly create a new one time, delete after run job that is created by the sucessfull job A to be executed at a specific time/date.
What happens if you want to start running "Job A" on three different schedules, depending on the day?
Also, again, it seems to me as if that requires you to hard-code the job details into the job -- to me, that's a very bad idea and will inevitably cause you errors and other grief in the future.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2013 at 6:12 am
The job A will run once a week and Job B should run the same day too. So adding a step to check if the job A ran today (the sameday when Job B is running) and was completed successfully , then run Job B will be helpful and if Job A is still running, Job B checks the status of Job A again after half an hour. Any Idea?
January 24, 2013 at 6:16 am
So JobB should only run if JobA has run successfully?
If JobA runs on a Saturday and finishes on a Sunday do you want to run JobB?
January 24, 2013 at 6:41 am
The TSQL I posted above will work for this, simple tweak here and there...just add in a join to sysjobhistory for cases where you are checking for something in the past (if it would even be needed)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 25, 2013 at 7:41 am
ScottPletcher (1/23/2013)
Perry Whittle (1/23/2013)
and all that's easier than a quick check in the dbo.sysjobhistory table??Yes: which specific entry for "Job A" does one check in sysjobhistory?
The last one? What if that's from the previous run of "job a", and "job a" has not run again since "job b" last ran?
I think it will be easier to quickly determine the relative status of job "a" vs job "b" with specific flags for each, rather than relying on assumptions about each job.
I guess I'm willing to assume, as you clearly must, that msdb.dbo.sysjobhistory is not accidentally cleared between "a" and "b"; although it may be more possible than you think, depending on the number of job failures/retries, delays, and the (default) "remove agent history" settings of the SQL Server Agent.
I get the following error "Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier" while executing this script.
January 25, 2013 at 8:57 am
Did you run it as-is?
You need to replace the string UID with the "proper" job_id from your msdb..sysjobs table - I have no idea what the job_id's stored in your msdb database are 😛
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply