5

Testing SQL Agent Job Step Output During The Job

 3 years ago
source link: https://thelonedba.wordpress.com/2020/08/25/testing-sql-agent-job-step-output-during-the-job/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK