I have a batch job that does some work to a table and once it's
finished, I want it to run a completely separate batch job, and then
return to the currently executing one.

The "starting" job has about 20 steps in it, and the new job has 3 steps
in it - it creates a blank excel file, builds data into that spreadsheet
and then emails it.

I want the "starting" job to run thru steps 1-16 before executing the
new job (Step 17), and once the new job is finished, to return back to
the "starting" job and continue with steps 18, 19, 20 and now 21. I
don't really care if the new Step 17 gives an error, as it's not
dependent on that step running for the rest of the job steps to execute.

How do I call the "new" job from within an existing SQL Agent job?

Any help/advice appreciated.

BC

Blasting Cap wrote:
Hi BC,

I came accross a similar requirement some time back for another DBA,
and he told me how to do this. I haven't tested this but I believe
this is how you do it:

step 17 should run the following T-SQL:

USE msdb
EXEC msdb.dbo.sp_start_job @job_name = 'new job'

If you want to wait for the 'new' job to be executed, then it gets a
little tricky. I think this will work, but you may have to play around
with it:

DECLARE @JobStatus int

EXEC msdb.dbo.sp_start_job @job_name = 'jbTest'
EXEC spGetJobStatus 'jbTest', @JobStatus OUTPUT

SELECT @JobStatus

WHILE @JobStatus <> 1
BEGIN

SELECT GETDATE()
EXEC spGetJobStatus 'jbTest', @JobStatus OUTPUT
SELECT @JobStatus

END


A lot of code is encapsulated in the 'spGetJobStatus' stored procedure:

CREATE PROCEDURE dbo.spGetJobStatus(
@job_name sysname,
@JobStatus int OUTPUT
) AS

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @is_sysadmin int
DECLARE @job_owner sysname

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id',
@job_name OUTPUT, @job_id OUTPUT

CREATE TABLE #Status( job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default
NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #Status
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner,
@job_id

SET @JobStatus = (SELECT job_state FROM #Status)

DROP TABLE #Status

RETURN 1
GO

The @JobStatus codes are:
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = [obsolete],
7 = PerformingCompletionActions

Hope this helps, good luck!

-Tav.-
Tavis Pitt

Tav:

This works perfectly, and does exactly what I want it to - to kick off a
job independently of the main job, but has to wait until the main job
finishes creating the input for my new one.

Many thanks,

BC



Tav wrote: