6

SQL Server 2012 R2 on Windows Server 2008R2

Is there any way to send notifications to different Emails:

  • on success of an Sql Server Agent job to one Email address and on failure - to another ?
  • on success (or failure) of a particular job step (but not the whole job)?

enter image description here

8

You have to use tsql to do that.

in the job step after your main job step add 2 more job steps:

enter image description here

step 2--success email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs success !!',
    @subject = 'Copy and Purge BAKs success',
-- to send file attachments 
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email. 
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

step 3-- failure email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs failed !!',
    @subject = 'Copy and Purge BAKs failed',
-- to send file attachments 
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email. 
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

and then use tsql sp_send_dbmail

Note: If you want HTML email to be send out, I have a script for that to help you out.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.