6

My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 completes.

Thank you.

3
  • Why not just make one job and have it be 10 steps? That's going to be the best way to do it. There are other ways to do it using some sort of WAIT logic, but the shortest distance from point a to point b is to just make it all one job with 10 steps. – Kris Gruttemeyer Oct 9 '14 at 12:38
  • Thank you Kris. But here i have more than 15 steps per 1job. if i have all jobs steps in single job it gonna be nearly 170 steps.And i already tried with WAITFOR DALEY option. but it not suitable for me – sarath Oct 9 '14 at 13:37
  • But here i have more than 15 steps per 1 job. this is a crucial information that you should put in your question. If you keep adding info, while other people are trying to help, it will get you no where. – Kin Shah Oct 9 '14 at 13:47
2

Have the last step of job 1 be to fire job 2, use this code:

EXEC dbo.sp_start_job N'MyJob 2'

Then, the last step of job 2 be to fire job 3, so on and so forth.

2
  • I don't want to add any steps to the jobs. I just want to run jobs in sequence manner using T-sql script. or with another job – sarath Oct 9 '14 at 13:48
  • Use Kin's approach, then. – Kris Gruttemeyer Oct 9 '14 at 13:49
2

I realize this question is a few years old at this point, but recently needed to schedule multiple daily maintenance jobs to run overnight in a specific sequence and came up with another way to accomplish this aside from the answers already provided. It's quick to set up and easy to reorder or change jobs as needed through the master job properties.

Not certain how this measures up to best practices, but it does log history for each job individually in addition to showing the combined execution time for all jobs in the master job history. I used this approach because each job in the sequence had different Notification settings (different recipient groups) so I couldn't run each job as an individual step within one single job.

  1. Create user-defined table type.

    CREATE TYPE [dbo].[JobSequenceTable] AS TABLE(
    [JobNumber] [TINYINT] IDENTITY(1,1) NOT NULL,
    [JobName] [VARCHAR](100) NULL
    )
    
  2. Create function to check job status (currently running or not).

    CREATE FUNCTION [dbo].[fnJobStatusCheck] (@JobName VARCHAR(100))
    RETURNS BIT
    AS
    BEGIN
    DECLARE @IsRunning BIT
    
        IF EXISTS (
                    SELECT J.[name] AS job_name
                    FROM msdb.dbo.sysjobs J
                    INNER JOIN msdb.dbo.sysjobactivity A ON A.job_id = J.job_id
                    WHERE A.session_id = (
                            SELECT TOP 1 session_id
                            FROM msdb.dbo.syssessions
                            ORDER BY agent_start_date DESC
                            )
                        AND A.start_execution_date IS NOT NULL
                        AND A.stop_execution_date IS NULL
                        AND J.[name] = @JobName
                    )
            BEGIN
                SET @IsRunning = 1
            END
    
        ELSE
            BEGIN
                SET @IsRunning = 0
            END
    
    RETURN @IsRunning
    END
    GO
    
  3. Create procedure to run a list of jobs in provided sequence.

    CREATE PROCEDURE [dbo].[sp_start_job_sequence]
    (
        @JobList JobSequenceTable READONLY
    )
    AS
    
    SET NOCOUNT ON
    
    BEGIN
        DECLARE  @JobNumber TINYINT = 1
                ,@JobName VARCHAR(100)
                ,@IsRunning BIT
    
        BEGIN
        WHILE (@JobNumber <= (SELECT MAX(JobNumber) FROM @JobList))
            BEGIN       
                SELECT @JobName = JobName FROM @JobList WHERE JobNumber = @JobNumber
    
                SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName)
    
                IF @IsRunning = 0
                BEGIN
                    EXEC msdb.dbo.sp_start_job @job_name = @JobName
                END
    
                WAITFOR DELAY '00:00:15.000';
                SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName)
    
                WHILE @IsRunning = 1
                BEGIN
                    WAITFOR DELAY '00:00:15.000';
                    SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName)
                END
    
                SET @JobNumber = @jobNumber + 1
            END
        END
    END
    GO
    
  4. Create master SQL job to execute existing jobs in sequence.

    DECLARE @JobList AS JobSequenceTable
    INSERT INTO @JobList
    VALUES 
     ('Job_Name_1') 
    ,('Job_Name_2')
    ,('Job_Name_3')
    ,('Job_Name_4')
    ,('Job_Name_5')
    ,('Job_Name_6')
    ,('Job_Name_7')
    ,('Job_Name_8')
    ,('Job_Name_9')
    ,('Job_Name_10')
    
    EXEC dbo.sp_start_job_sequence @JobList
    
2
  • This is exactly a design I had in my head and am so happy to find it. Did this solution work for you? Any issues, problems, gotchas after a couple of years? This is a very clear solution so I hope it has worked well. – IanG Mar 29 '19 at 3:40
  • No issues to report, still working as expected. – Ashlee May 15 '20 at 15:50
1

You can follow a master slave model .. where in you would create a master job that drives the jobs.

so create a master job with below steps

--- step 1
EXEC dbo.sp_start_job N'Your_JOB_1' ;
GO
--- To be 100% accurate, msdb.dbo.sysjobactivity can be queried to make sure a particular job is completed before starting another one. 

--- step 2
    EXEC dbo.sp_start_job N'Your_JOB_2' ;
    GO
--- step 3
    EXEC dbo.sp_start_job N'Your_JOB_3' ;
    GO
--- step 4
    EXEC dbo.sp_start_job N'Your_JOB_4' ;
    GO

Your 10 jobs will be there which will be driven by your master job.

Make sure you do proper error reporting. e.g. if Job 1 fails, then just fail the entire job or go to some other step to get you notified .. something along those lines.

1
  • 2
    IIRC sp_start_job is asynchronous. It does not wait for the started job to finish before it returns. – Michael Green Oct 10 '14 at 10:37
1

I have not yet found a notification or activation on job completion. The nearest I can think of is putting a tirgger on the job-related systems tables in MSDB. This is very dodgy, however, and not at all recommended, and may not be possible.

In the past, when I've needed to merge two job streams that I can't edit, I've used a check-and-wait stored procedure like this:

create procedure dbo.WaitForJob
    @JobName sysname
as

set nocount on;

while exists(
    select
        1
    from msdb.dbo.sysjobactivity ja
    inner join msdb.dbo.sysjobs j
        on j.job_id = ja.job_id
    where j.name = @JobName
    and ja.start_execution_date < GETDATE()
    and ja.stop_execution_date is NULL
    )
begin
    WAITFOR DELAY '00:00:15';  -- whatever duration you're OK with
end

-- Check @JobName's status and RAISERROR on failure

go

I'm sure you can add in the appropriate actions for when the monitored job has failed. You may also like to add in a timeout so this SP doesn't run indefinitely. This may be one of the exceedingly rare occasions where it is appropriate to use the sp_ prefix. I'll leave that to your discretion.

Now the T-SQL to run your existing jobs becomes

exec dbo.sp_start_job @job_name = 'job1';
exec dbo.WaitForJob @JobName = 'job1';

exec dbo.sp_start_job @job_name = 'job2';
exec dbo.WaitForJob @JobName = 'job2';

...

exec dbo.sp_start_job @job_name = 'job10';
-- no need to wait

.. with appropriate error handling and return code checking, of course. This can be packaged as a single batch, a job with one step per triggered job or one step per statement. I prefer the last as restart is simpler.

0

I wrote the below which I have since parameterised and works just fine.

DECLARE @RunInd CHAR(1);
SET @RunInd = (SELECT   CASE    WHEN sja.start_execution_date IS NULL THEN 'N' --Indicating Not Running
                                WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'R' --Indicating Running
                                WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'N' --Indicating Not Running
                        END AS 'RunStatus'
                FROM    msdb.dbo.sysjobs sj
                        JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
                WHERE   session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
                    AND sj.Name IN ('Job_That_Is_Running'))
WHILE @RunInd = 'R'
BEGIN
    WAITFOR DELAY '00:00:30'
    SET @RunInd = (SELECT   CASE    WHEN sja.start_execution_date IS NULL THEN 'N' --Indicating Not Running
                                    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'R' --Indicating Running
                                    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'N' --Indicating Not Running
                            END AS 'RunStatus'
                    FROM    msdb.dbo.sysjobs sj
                            JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
                    WHERE   session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
                        AND sj.Name IN ('Job_That_Is_Running'))
END
EXEC msdb.dbo.sp_start_job @job_name='Next_Job_to_Start'
1
  • I don't see how this code will do what what the asker wants. – Colin 't Hart Jan 25 '17 at 10:43
0

For extremely powerful job workflow management use a SSIS workflow job. Unlike SQL Agent Jobsteps, Workflow tasks can run in parallel, wait to sync up, then execute other steps. You are not limited to Success/Failure to determine the next step. You can add all sorts of functions that let you make decisions. From simple, only do this on a Sunday, to powerful queries into your control tables to make decisions. The Graphic design tool makes it much easier to follow complex logic. SSIS is not just for data movement.

While SSIS does provide some nice job monitoring. You can really step it up with PragmaticWorks (now SentryOne) tool called BIxPress. This lets operations folk watch task execution in real-time. I've used this extensively in large corporate data warehouses. The ability to drill into tasks that failed overnight made troubleshooting a breeze.

Disclaimer: I have never worked for these guys. But knew them as MS Partners.

0

AS others have posted, this is an old question, but I don't see where anyone has suggested this method yet, so here goes.

At the end of your first job, add one step that calls Job #2, then so on and so forth.

If you need the next job to run REGARDLESS of whether or not a step in Job #1 fails, change it so that when a step fails, it jumps to the appropriate job step (either the next in the line or to Job #2).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.