call a job within a job? 1

[LucasH]

Hi,

I have a several jobs created that each have several steps (DTS package, SP, etc). I want to create a "master" job that calls these sub-jobs. How do I do that? Is there syntax to call a job?

 

[mrdenny]

The T/SQL command to start a job is sp_start_job. Look in BOL for the syntax.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LucasH]

thanks a lot.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LucasH]

So I created this job that has steps to start other jobs. However, I don't want the next job to start until the job, the first step fired, is finished executing. What do you think?

 

[mrdenny]

That would require writing a loop that checks the sp_help_job to see if the job it just started is complete. It it is then exit the loop. If it's not, then sleep for a small amount of time, and then repeat the loop.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LucasH]

Basic sql question here....

I have looked into the sp_help_job sp and that should work great. What is the best strategy when querying a stored procedure? I just want to look at one column of the result set of sp_help_job, what is the best way to do that?

Do I have to create a temp table for the entire result set, then query that table or can I query the result set directly, ie

SELECT last_run_outcome from sp_help_job @jobname = 'myjob', etc?

 

[icemel]

To simplify your task, you can create the job in EM... it's easy to create separate steps without having to write loops. However, instead of calling the job in the step, you need to put the actual code of the job in the step. That way it won't go to the next step until the code is finished executing. If you just call the job, it will go to the next step as soon as the job is executed.

However, if you really want to write it yourself to find out if your job is running, you need to query the sysjobhistory table directly. You can't pick and choose columns using sp_help_job, unfortunately.

 

[LucasH]

Some of the steps are quite involved so I would prefer to keep them out of the job steps themselves.

I have a solution to my problem, if you want to take a look...

This sp checks to see if a job is running....

CREATE PROCEDURE JobRunning
@jobname nvarchar(50)
AS
Declare
@jobid AS uniqueidentifier,
@State As int

--get job id from name
SELECT @jobid = job_id
From sysjobs
WHERE name = @jobname

--create temp table
create table #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)

--insert job details into temp table by running extended job (must supply a couple paramaters, <is sysadmin 1 or 0>, and job owner
insert into #enum_job
execute master.dbo.xp_sqlagent_enum_jobs 1,'PADRES\Sysadmin'

--insert execution state into variable
SELECT @State = #enum_job.State FROM #enum_job
WHERE #enum_job.job_id = @jobid

drop table #enum_job

--if job is running, return 1, else return 0
if @State < 4
RETURN 0
ELSE
RETURN 1
GO


I start the job with this sp. It runs the above sp to see if it's running and when it stops running, it checks to see if it's successful. If it is successful, the MASTER job continues, if not, the MASTER job stops. Pretty cool, I think...

CREATE PROCEDURE StartJob @jobname varchar(50) AS

--start job
exec msdb.dbo.sp_start_job @job_name = @jobname

DECLARE @return_status int
DECLARE @outcome int
DECLARE @jobid uniqueidentifier
DECLARE @outcome_message nvarchar(25)

SET @return_status = 0
SET @outcome = 0
SET @outcome_message = NULL

--get job id from job name

SELECT @jobid = job_id
From msdb.dbo.sysjobs
WHERE name = @jobname

--check to see if job is running, if it's idle, exit while loop
WHILE @return_status = 0
BEGIN
WAITFOR DELAY '000:00:30'
exec @return_status = msdb.dbo.JobRunning @jobname --check to see if job is idle 0 means not idle, 1 means idle
END

--check to see if last outcome was successful
SELECT @outcome = last_run_outcome FROM msdb.dbo.sysjobservers WHERE job_id = @jobid

--if last outcome is not successful stop job
if @outcome <> 1
BEGIN
exec msdb.dbo.sp_stop_job @job_name = 'StartAllJobTest'
END

GO

 

[icemel]

Looks good to me.