database-mailjobssql serversql-server-agent
I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process failed?
Something more than "One or more of the job steps in the job was not successful."
I'm sure I can add another step that queries the job history table and sends another email but I'm trying to combine the default email with a bit more information.
Is there any way to extract the full text of these messages?
You can achieve it in 2 ways - Go in job step and select the Advanced tab:
a. Output to a file (<== My preferred method)
b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of nvarchar(1024))
In order to see the additional logged information you need to use this stored procedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogs table directly.
Yes you can achieve this with help of SP:
CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
BEGIN TRY
/*
Perform some action that might fail
*/
SELECT 0/0; --THIS WILL FAIL
END TRY
BEGIN CATCH
DECLARE @subject nvarchar(max) = 'Job Failure Notification';
DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed'
+ CHAR(10) + CHAR(13) + 'Error Number: ' + CAST(ERROR_NUMBER() AS nvarchar(max))
+ CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
DECLARE @to nvarchar(max) = 'somebody@email.com';
DECLARE @profile_name sysname = 'SQLMailProfileName';
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
@recipients = @to, @subject = @subject, @body = @body;
END CATCH
END
You can refer here how it is being used.
Adding@ You can recieve email if Database mail is configured in very first place. On how to set that up read How to Set Up Database Mail for SQL Server Job Failures
Also, In SQL Agent Job Properties-> step > edit> Advanced --you can also check Include step output in history to get the message from step failure.
Leave the On failure action to Quit the job reporting failure.
Best Answer
I've always done this by making a step's next action on failure be a specific step that sends an e-mail about that step only, then that step's on complete action is to continue to {failure step + 1}. Yes it requires some manual interaction with the jobs up front, but I can't think of any way to change the defaults without some level of manual tinkering.