I came across a need for SQL Server Agent job output to be emailed out to the developer who created the code that the job was running. For various reasons, they don’t necessarily have access to the SQL Server itself – hurrah – but need to see what’s being generated by their scripts in case something goes wrong.
So, I ended up writing this extremely rough and ready SP:
USE master;
SET QUOTED_IDENTIFIER ON;
IF OBJECT_ID('SendJobOutput', 'P') IS NULL
EXEC ('CREATE PROCEDURE SendJobOutput AS BEGIN SELECT 1 END');
GO
ALTER PROCEDURE SendJobOutput
@StartDate INT,
@StartTime INT,
@job_name sysname,
@recipients VARCHAR(MAX)
AS
BEGIN
/*
SP: SendJobOutput
Parameters:
@StartDate int - msdb-formatted date at which the job starts
@StartTime int - msdb-formatted time at which the job starts
@job_name sysname
@recipients varchar(max) - string full of semi-colon-separated email addresses to get the output message
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 @recipients varchar(max) = 'user@address.bar; someoneelse@foo.com';
exec SendJobOutput @StartDate, @StartTime, @job_name, @recipients
*/
DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
DECLARE @Subject sysname = N'Job output - ' + @job_name;
DECLARE @job_id UNIQUEIDENTIFIER =
(
SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
);
DECLARE @message NVARCHAR(MAX)
= N'<html><body><table><tr><th>JobName</th><th>DateTime</th><th>Step Name</th><th>Message</th></tr>';
DECLARE @results NVARCHAR(MAX) = N'';
SELECT @results = @results + STUFF(
(
SELECT j.name AS TD,
'',
msdb.dbo.agent_datetime(run_date, run_time) AS TD,
'',
jh.step_name AS TD,
'',
message AS TD
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 jh.job_id = @job_id
ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
step_id
FOR XML PATH('TR'), ELEMENTS
),
1,
0,
''
);
SELECT @message = @message + @results + N'</table></body></html>';
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DoNotReply - SQLMail', -- name of email profile already defined in msdb.dbo.sysmail_profile
@recipients = @recipients,
@subject = @Subject,
@body_format = 'HTML',
@body = @message;
END;
At some point, I’ll revisit it to smarten up the email message being generated, but this at least is usable.
Instructions for use are in the header block, but, spoiler alert, it involves adding an extra step at the end of a job, and pasting in a few lines of script which makes use of the SQLAgent job step macros/tokens.
Note the hack to get multiple “TD” elements – if those extra You’ll need to configure the email profile being used. And, in case WordPress corrupts the script above (as if…), it’s over on my github page too as “SendJobOutputByEmail.sql”''s aren’t selected, the whole things gets blended into a single which is not right.
2 Responses to SQL Server Agent Job output to email
Leave a comment
The Lone DBA 
Pingback: Sending an E-Mail with SQL Agent Job Outputs – Curated SQL
Pingback: Testing SQL Agent Job Step Output During The Job | The Lone DBA