Execute SQL Job synchronously for BizTalk via a Stored Procedure call

The async one was very easy to do.  However, in the process automation task I was given, there was a need to execute SQL Job and waiting for the execution result.  I couldn't find anything on the web to do what I wanted, so I make the following stored procedure.

In addition, this stored procedure is quasi-synchronous.  I start the job and then check the status.  You can change the timing of the status check, but for the example below, it is "synchronous within 30 seconds."  I wish there is a better way to do this, but at my current knowledge base I only came up with this:

 

CREATE PROCEDURE [SQLJobExecSyncByName]
    @SQLJobName AS [varchar](128),
    @SQLJobStepName AS [varchar](128),
    @MinutesToWait AS [int],
    @JobExecStatus AS [bit] output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    DECLARE @JobStartDateTime AS DATETIME
    DECLARE @SPQuitDateTime AS DATETIME
    DECLARE @JobStartDateAsString AS VARCHAR(8)
    DECLARE @JobStartTimeAsString AS VARCHAR(6)
    DECLARE @SPOutcome AS BIT
    SET @JobStartDateTime = DATEADD(mi, -1, GETDATE())
    SET @SPQuitDateTime = DATEADD(mi, @MinutesToWait, GETDATE())
    SET @SPOutcome = 0
    SELECT @JobStartDateAsString = CONVERT(VARCHAR(8), @JobStartDateTime, 112)
    SELECT @JobStartTimeAsString = REPLACE(CONVERT(VARCHAR(8), @JobStartDateTime, 108), ':', '')

    -- Create a temp table to store result
    CREATE TABLE #Result_sp_help_jobhistory
    (
        instance_id [int]
        , job_id [uniqueidentifier]
        , job_name [sysname]
        , step_id [int]
        , step_name [sysname]
        , sql_message_id [int]
        , sql_severity [int]
        , [message] [nvarchar](1024)
        , run_status [int]
        , run_date [int]
        , run_time [int]
        , run_duration [int]
        , operator_emailed [nvarchar](20)
        , operator_netsent [nvarchar](20)
        , operator_paged [nvarchar](20)
        , retries_attempted [int]
        , [server] [nvarchar](30)
    )

    -- Start the job @SQLJobName at step @SQLJobStepName
    EXEC msdb.dbo.sp_start_job
        @job_name = @SQLJobName,
        @server_name = @@SERVERNAME,
        @step_name = @SQLJobStepName;
   
    WHILE (GETDATE() < @SPQuitDateTime) -- not time to quit yet
    BEGIN
        -- Check to see if the job has finished successfully
        INSERT INTO #Result_sp_help_jobhistory
            (instance_id
                , job_id
                , job_name
                , step_id
                , step_name
                , sql_message_id
                , sql_severity
                , [message]
                , run_status
                , run_date
                , run_time
                , run_duration
                , operator_emailed
                , operator_netsent
                , operator_paged
                , retries_attempted
                , [server]
            )
        EXEC msdb.dbo.sp_help_jobhistory
            @job_name = @SQLJobName,
            @start_run_date = @JobStartDateAsString,
            @start_run_time = @JobStartTimeAsString,
            --@run_status = 1, -- Success
            @mode = 'FULL'

        IF(EXISTS(SELECT *
                    FROM #Result_sp_help_jobhistory
                    WHERE job_name = @SQLJobName
                        AND step_name = '(Job outcome)'
                        AND run_status = 1
                        AND @JobStartDateTime < CONVERT(DATETIME, CONVERT(VARCHAR(8), run_date) + ' ' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 1, 2) + ':' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 3, 2) + ':' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 5, 2), 112)
                )
            )
        BEGIN
            -- The job has succceeded at least once after it was called
            SET @SPOutcome = 1
            GOTO FinishingStoredProcedureCall;
        END
       
        IF(EXISTS(SELECT *
                    FROM #Result_sp_help_jobhistory
                    WHERE job_name = @SQLJobName
                        AND step_name = '(Job outcome)'
                        AND run_status = 0
                        AND @JobStartDateTime < CONVERT(DATETIME, CONVERT(VARCHAR(8), run_date) + ' ' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 1, 2) + ':' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 3, 2) + ':' + SUBSTRING(CONVERT(VARCHAR(6), run_time), 5, 2), 112)
                )
            )
        BEGIN
            -- The job has failed after it was called
            GOTO FinishingStoredProcedureCall;
        END
       
        -- Clean up for the next run
        TRUNCATE TABLE #Result_sp_help_jobhistory
       
        -- check every 30 seconds
        WAITFOR DELAY '00:00:30';
    END
   
    FinishingStoredProcedureCall:
        SET @JobExecStatus = @SPOutcome
        DROP TABLE #Result_sp_help_jobhistory;
   
END

 

This SP was made to return an output parameter as result as to maximize reusability of the logic.  In order to use this from BizTalk, it was hooked into the following SP:

CREATE PROCEDURE [SQLJobExecSyncByNameForBizTalk]
    @SQLJobName AS [varchar](128),
    @SQLJobStepName AS [varchar](128),
    @MinutesToWait AS [int]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    DECLARE @JobExecStatus AS BIT

    EXEC [ODS].[SQLJobExecSyncByName]
        @SQLJobName = @SQLJobName,
        @SQLJobStepName = @SQLJobStepName,
        @MinutesToWait = @MinutesToWait,
        @JobExecStatus = @JobExecStatus OUTPUT;
       
    SELECT CONVERT(VARCHAR(8), @JobExecStatus)
         FOR XML PATH(N''), ROOT(N'JobExecStatus');
       
END

Print | posted on Monday, November 1, 2010 3:18 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski