May 6, 2008 at 12:53 pm
Is it possible to automate a job to run right after another finishes? I have 3 jobs to run right after the other,however the times differ greatly day by day. Any articles I can take a look at would help as well. I am not finding anything or not searching correctly.
Thanks in advance.
¤ §unshine ¤
May 6, 2008 at 12:59 pm
It can be done if you create three different jobs with their schedules and call them with the folowing workflow. For example:
Job1 Step 1 execution--On successful completion --> Go to next step
Step 2 sp_start_job 'Job2'
Job2 Step 1 execution--On successful completion --> Go to next step
Step 2 sp_start_job 'Job3'
Job3 Step 1 execution--On successful completion --> Quit the job
reporting success.
Manu
May 6, 2008 at 1:01 pm
Thank you so much! I just found another similar post here.
http://www.sqlservercentral.com/Forums/Topic494729-5-1.aspx
thanks again!
¤ §unshine ¤
June 9, 2008 at 7:45 am
I tried this and it starts the execute statement and marks as successful as soon as it is run, all jobs were run about same time. It does not wait for the actual job to finish to go to the next step to start the next job.
Is there a t-sql script I can add to this to be able to have step 2 start when the actual job being executed in step 1 is complete? Maybe an end time check?
¤ §unshine ¤
June 9, 2008 at 10:21 am
You could use a query like this that checks sysjobhistory:
select distinct j.Name as "Job Name", h.run_date as LastStatusDate,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as JobStatus
from sysJobs j join sysJobHistory h
on j.job_id = h.job_id and j.name = 'jobname' and h.run_date =
(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)
order by 1
Greg
June 9, 2008 at 11:54 am
I am trying this with errors. I'm not sure where to put the case/exec statement.
select distinct j.Name as 'SNTest', h.run_date as LastStatusDate,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as JobStatus
from sysJobs j join sysJobHistory h
on j.job_id = h.job_id and j.name = 'SNTest' and h.run_date =
(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)
order by 1
Select
CASE JobStatus
WHEN 'Successful' THEN
BEGIN
EXEC sp_start_job @job_name = 'SNTest#2'
END
Else
BEGIN
END
END
GO
¤ §unshine ¤
June 10, 2008 at 8:47 am
Use IF...ELSE instead of CASE for conditional logic outside a SELECT statement.
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply