Stored Procedures (2) to sequentially run SQL Server Agent Jobs.

use [master];
go

-- ============================================================================
-- Stored Procedure to Get SQL Job Execution Status
-- http://adventuresinsql.com/2010/01/stored-procedure-to-get-sql-job-execution-status/
-- ============================================================================
create procedure dbo.sp_dba_GetSqlJobExecutionStatus(
    @job_name sysname,
    @select_data int = 0,
    @execution_status int = null output
)
as
begin
    set nocount on;

    /*-------------------------------------------------------
    Is the execution status for the jobs.

        Value   Description

        0       Returns only those jobs that are not idle or suspended.
        1       Executing.
        2       Waiting for thread.
        3       Between retries.
        4       Idle.
        5       Suspended.
        7       Performing completion actions
    -------------------------------------------------------*/

    declare @job_id uniqueidentifier,
            @is_sysadmin int,
            @job_owner sysname;

    select @job_id = jv.job_id,
        @job_owner = sp.name
    from msdb.dbo.sysjobs_view as jv
    inner join sys.server_principals as sp
        on jv.owner_sid = sp.sid
    where jv.name = @job_name;

    select @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);

    create table #xp_results(
        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
    );

    insert into #xp_results
        execute master.dbo.xp_sqlagent_enum_jobs
            @is_sysadmin,
            @job_owner,
            @job_id;

    select @execution_status = job_state from #xp_results;

    drop table #xp_results;

    if @select_data = 1
        begin
            select @job_name as job_name, @execution_status as execution_status
        end;
    set nocount off;
end;
go

exec sp_MS_marksystemobject 'sp_dba_GetSqlJobExecutionStatus';

-- ============================================================================
-- Stored Procedure to Sequentially Run SQL Agent Jobs
-- http://adventuresinsql.com/2010/01/stored-procedure-to-sequentially-run-sql-agent-jobs/#comment-208821
-- ============================================================================
create procedure dbo.sp_dba_run_unscheduled_jobs
    @job_name_prefix sysname,
    @max_minutes_runtime int = null,
    @days_in_job_cycle int = 1,
    @mail_job_name varchar(256) = '',
    @mail_profile_name sysname = null,
    @mail_recipients varchar(max) = null
as
begin
    declare @job_name sysname,
            @execution_status int,
            @row_count int,
            @last_run_date int,
            @start_time datetime,
            @stop_time datetime,
            @min_date datetime,
            @min_int_date int,
            @int_date int;

    select @min_date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),
        @days_in_job_cycle = case when @days_in_job_cycle < 1 then 1 else @days_in_job_cycle end; -- Prevent Infinite Loop

    select
        @min_int_date = YEAR(@min_date) * 10000 + MONTH(@min_date) * 100 + DAY(@min_date) - @days_in_job_cycle,
        @int_date = YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE());

    select @row_count = 1,
        @job_name = '',
        @start_time = GETDATE(),
        @stop_time = DATEADD(mi, @max_minutes_runtime, GETDATE());

    while @row_count > 0 and (@stop_time is null or @stop_time > GETDATE())
        begin
            select top 1 @job_name = sj.name,
                @last_run_date = sjh.run_date
            from msdb.dbo.sysjobs as sj
            left outer join msdb.dbo.sysjobschedules as sjs
                on sj.job_id = sjs.job_id
            left outer join msdb.dbo.sysschedules as ss
                on sjs.schedule_id = ss.schedule_id
                   and ss.enabled = 1
            left outer join (select job_id,
                                 MAX(run_date) as run_date
                             from msdb.dbo.sysjobhistory
                             where step_id = 0 and run_date > @min_int_date
                             group by job_id) as sjh
                on sj.job_id = sjh.job_id
            where(sjs.job_id is null or ss.schedule_id is null)
                 and sj.name like @job_name_prefix
                 and (sjh.run_date is null
                      or sj.name > @job_name
                      or sjh.run_date < @int_date)
                 and sj.enabled = 1
            order by
                ISNULL(sjh.run_date, 0),
                sj.name;

            select @row_count = @@ROWCOUNT;

            if @row_count > 0
                begin
                    exec dbo.sp_dba_GetSqlJobExecutionStatus
                        @job_name = @job_name,
                        @execution_status = @execution_status output;

                    if @execution_status = 4
                        begin --Make sure job is not running
                            exec msdb.dbo.sp_start_job @job_name = @job_name
                        end;

                    waitfor delay '00:00:02.000'; -- Pause here to make sure the job gets started before checking the status

                    exec dbo.sp_dba_GetSqlJobExecutionStatus
                        @job_name = @job_name,
                        @execution_status = @execution_status output;

                    while @execution_status != 4
                        begin
                            waitfor delay '00:00:01.000';
                            exec dbo.sp_dba_GetSqlJobExecutionStatus
                                @job_name = @job_name,
                                @execution_status = @execution_status output;
                        end;
                end;

            if @stop_time is not null
               and @stop_time > GETDATE()
                begin
                    declare @subject nvarchar(255), @body nvarchar(max);

                    select
                        @subject = @mail_job_name+' on '+@@SERVERNAME+' - Shutting down...Time limit reached.',
                        @body = @mail_job_name+' on '+@@SERVERNAME+' shut down after '+CAST(ABS(DATEDIFF(mi, GETDATE(), @start_time)) as nvarchar)+' minutes.';

                    exec msdb.dbo.sp_send_dbmail
                        @profile_name = @mail_profile_name,
                        @recipients = @mail_recipients,
                        @subject = @subject,
                        @body = @body;
                end;
        end;
end;
go

exec sp_MS_marksystemobject 'sp_dba_run_unscheduled_jobs';
go