I have set up my SQL Server Agent to send notification emails whenever a job fails, but the messages in the emails are so not detailed, and it's not more than something like:

MESSAGES:         The job failed.  The Job was invoked by User domain\user.  The last step to run was step 1 (jobName).

however if I open the history of that failing job, I can drill-down and see the details and the error message of the specific failing step inside that job.... the question is that, how can I get the error message of the STEP (not just the job) appear in the notification email?

• Edited by Butmah 22 hours 3 minutes ago

Use this trigger will send email alert to email 

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------- 
-- Object Type : job failure alert 

---------------------------------------------------------------------------- 

create  trigger [dbo].[trg_stepfailures]
on [dbo].[sysjobhistory]
for insert
as
declare @strMsg varchar(400),
@strRecipient varchar(128),
@copyRecipient varchar(128),
@bccRecipient varchar(128)

set @strRecipient = 'dinesh@t.com'
set @copyRecipient = 'dinesh@t.com'
set @bccRecipient = 'dinesh@t.com'

if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = 
convert(char(10),'Server') + char(58) + @@servername +
char(10) +
convert(char(10),'Job') + char(58) + convert(varchar(50), sysjobs.name) + 
char(10) +
convert(char(10),'Step') + char(58) + convert(varchar(50), inserted.step_name)+
char(10) +
convert(char(10),'Message') + char(58) + convert(varchar(150), inserted.message)
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0

raiserror (@strMsg, 16, 10) with log
exec msdb.dbo.sp_send_dbmail
@profile_name = N'SQL DBA SUPPORT',
@recipients = @strRecipient,
@copy_recipients = @copyRecipient,
@blind_copy_recipients = @bccRecipient,
@body = @strMsg,
@subject = 'Job Failure on server'

end 

• Edited by Dinesh Vishe 21 hours 40 minutes ago

Hello - Here are the steps:

1. Configure Database Mail
2. Create a SQL Server job
3. Adjust the properties within the SQL Server Agent
4. Create an Operator
5. Adjust the SQL Server job to send on Failure

More details and demo can be found here:

http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-failures/

Hello - Here are the steps:

1. Configure Database Mail
2. Create a SQL Server job
3. Adjust the properties within the SQL Server Agent
4. Create an Operator
5. Adjust the SQL Server job to send on Failure

More details and demo can be found here:

http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-fai

Hello - Regret I overlooked the post somehow

Just a thought here:

You can put your code in some stored procedure or may be add some logic to handle the exception and also trigger mail to your ID in case failure happens, something like below:

-- Your code stars here
BEGIN TRY
	-- Your actual business logic
	-- Sample failure illustration 
	SELECT 0/0; 
END TRY

BEGIN CATCH
	DECLARE @subject nvarchar(max) = 'Job Failed Notification';
	DECLARE @body nvarchar(max) = '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) = 'you@company.com';
	DECLARE @profile_name sysname = 'MailProfile1';
	EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
	@recipients = @to, @subject = @subject, @body = @body;
END CATCH
-- Your code ends here

Hope this helps

Thanks Manu... but this is not so practical, what if my job is running a number of SSIS packages each in a step, or what if it is a job for a software that we bought and did not develop it our self?! this means that I have to mess with those jobs which _if possible_ I don't think the developers will be happy with that ... and again sometimes this suggestion can't be applied.

There should be away to tell the SQL Server Agent to sent by email what it writes to the history log!

• Edited by Butmah 19 minutes ago