Start Job and Wait

/****** Object: StoredProcedure [dbo].[sp_start_job_and_wait] Script Date: 06/07/2012 14:38:49 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE PROCEDURE [dbo].[sp_start_job_and_wait]( --, @result int output)



----------------------------------------------------------------------------

-- OBJECT NAME: dbo.TBX_pos_import

-- AUTHOR: Harsha Majety

-- DATE: 

--

-- INPUTS: 

    @job nvarchar(128),

    @maxwaitmins int =3

-- OUTPUTS: 1 -> OK

-- 0 -> Still running after max mins or error

-- DEPENDENCIES: 

--

-- DESCRIPTION: Job to start a job and wait till it finishes

--

-- OTHER: 

----------------------------------------------------------------------------

)



AS 

BEGIN



set NOCOUNT ON;

set XACT_ABORT ON;



    BEGIN TRY



    declare @running as int

    declare @seccount as int

    declare @maxseccount as int

    declare @start_job as bigint

    declare @run_status as int



    set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())



    set @maxseccount = 60*@maxwaitmins

    set @seccount = 0

    set @running = 0



    declare @job_owner sysname

    declare @job_id UNIQUEIDENTIFIER



    set @job_owner = SUSER_SNAME()



    -- get job id

    select @job_id=job_id

    from msdb.dbo.sysjobs sj

    where sj.name=@job



    -- invalid job name then exit with an error

    if @job_id is null

        RAISERROR (N'Unknown job: %s.', 16, 1, @job)



    -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table

    declare @xp_results TABLE ( 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)



    -- start the job

    declare @r as int

    exec @r = msdb..sp_start_job @job



    -- quit if unable to start

    if @r<>0

        RAISERROR (N'Could not start job: %s.', 16, 2, @job)



    -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)

    WAITFOR DELAY '0:0:01';

    set @seccount = 1



    -- check job run state

    insert into @xp_results

    execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id



    set @running= (SELECT top 1 running from @xp_results)



    while @running<>0 and @seccount < @maxseccount

    begin

        WAITFOR DELAY '0:0:01';

        set @seccount = @seccount + 1



        delete from @xp_results



        insert into @xp_results

        execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id



        set @running= (SELECT top 1 running from @xp_results)

    end



    -- result: not ok (=1) if still running



    if @running <> 0 begin

        -- still running

        return 0

    end

    else begin



        -- did it finish ok ?

        set @run_status = 0



        select @run_status=run_status

        from msdb.dbo.sysjobhistory

        where job_id=@job_id

         and cast(run_date as bigint) * 1000000 + run_time >= @start_job



        if @run_status=1

            return 1 --finished ok

        else --error

            RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)



    end



    END TRY

    BEGIN CATCH



 DECLARE

 @ErrorMessage NVARCHAR(4000),

 @ErrorNumber INT,

 @ErrorSeverity INT,

 @ErrorState INT,

 @ErrorLine INT,

 @ErrorProcedure NVARCHAR(200);



 SELECT

 @ErrorNumber = ERROR_NUMBER(),

 @ErrorSeverity = ERROR_SEVERITY(),

 @ErrorState = ERROR_STATE(),

 @ErrorLine = ERROR_LINE(),

 @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');



 SELECT @ErrorMessage =

 N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +

 'Message: '+ ERROR_MESSAGE();



 RAISERROR

 (

 @ErrorMessage,

 @ErrorSeverity,

 1,

 @ErrorNumber, -- original error number.

 @ErrorSeverity, -- original error severity.

 @ErrorState, -- original error state.

 @ErrorProcedure, -- original error procedure name.

 @ErrorLine -- original error line number.

 );



    END CATCH



end