Testing SQL Agent Job Step Output During The Job

(This follows on from my previous script about emailing SQL Job ouput.)

There are times when a SQL server scheduled task needs to look at the output from the previous step, and make decisions based on that – particularly when parsing output from noisy/chatty scripts.

I have, therefore, put together a quick and dirty script to check the output from a SQL Server job step, and pull that into a string, so that it can be examined and a script can then make decisions about what to do next.

CREATE FUNCTION [dbo].[JobStepOutput]
(
    @StartDate INT,
    @StartTime INT,
    @job_name sysname,
    @step_id INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    /*
	FN: JobStepOutput
	Parameters:
			@StartDate int - msdb-formatted date at which the job starts
			@StartTime int - msdb-formatted time at which the job starts
			@job_name sysname
			@step_id int - step to look at.  If zero, then all.

	Usage:
			To be used in a SQL Agent job step:

			declare @StartDate int = CONVERT(int, $(ESCAPE_NONE(STRTDT)));
			declare @StartTime int = CONVERT(int, $(ESCAPE_NONE(STRTTM)));
			declare @job_name sysname = '$(ESCAPE_NONE(JOBNAME))';
			declare @StepID int = CONVERT(int, $(ESCAPE_NONE(STEPID))) - 1;

			select JobStepOutput(@StartDate, @StartTime, @job_name, @StepID);

*/

    DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
    DECLARE @job_id UNIQUEIDENTIFIER =
            (
                SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
            );
    DECLARE @EndDateTime DATETIME =
            (
                SELECT ISNULL(MIN(msdb.dbo.agent_datetime(run_date, run_time)), GETDATE())
                FROM msdb.dbo.sysjobhistory
                WHERE msdb.dbo.agent_datetime(run_date, run_time) > @StartDateTime
                      AND job_id = @job_id
                      AND step_id = 0
            );

    DECLARE @results NVARCHAR(MAX) = N'';

    SELECT @results = STUFF(
    (
        SELECT message
        FROM msdb.dbo.sysjobs j
            INNER JOIN msdb.dbo.sysjobhistory jh
                ON j.job_id = jh.job_id
        WHERE 1 = 1
              AND msdb.dbo.agent_datetime(run_date, run_time) >= @StartDateTime
              AND msdb.dbo.agent_datetime(run_date, run_time) < @EndDateTime
              AND jh.job_id = @job_id
              AND
              (
                  (
                      jh.step_id = @step_id
                      AND @step_id >= 0
                  )
                  OR (@step_id = -1)
              )
        ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
                 step_id
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'),
    1   ,
    0   ,
    ''
                           );

    RETURN (@results);

END;
GO

Some notes:

• I’ve shown STEPID - 1 in the example; this gives the previous job step.
• If you pass in -1, it’ll return all the job step output for this job
• I’ve tweaked the core sysjobhistory query from the previous post; this now looks for the end date of the job so you can more easily refer to previous job runs as well. Probably not needed, but came in handy while testing.

In case WordPress does something weird, it’s over on my github repo as well.

Postscript

While putting this script together, I noticed (in SQL2016) a function called “fn_sqlagent_jobs”, and hoped it might be useful. It didn’t appear to do anything, so wasn’t. And so I ended up writing the above.