> 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.

> Blasting Cap wrote:
>> 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.
>
> 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
>