locked
send email after job fails RRS feed

  • Question

  • Hi experts,

     

    I had a relatively low number of jobs that’s why I never set up any alert, I would just manually check every now and then.

     

    Now I have a few critical jobs that I need to know immediately the moment they fail…

     

    (I am on sql server 2016 and we plan to upgrade to 2017 in November).

     

    I know there is msdb.dbo.sp_send_dbmail  but not sure if it will be deprecated… What’s the best way to send an email after the job fails…?

    Friday, June 8, 2018 10:22 AM

Answers

  • sp_send_dbmail  isn't deprecated and you can use it, but the easier way is to use build-in Feature of Job notification: Notify an Operator of Job Status

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Mohsin_A_Khan Saturday, June 9, 2018 10:56 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 7:01 AM
    Friday, June 8, 2018 10:28 AM
  • sp_send_dbmail   is not deprecated and its not in the road map too yet for deprecation in any of the future releases.

    But even otherwise, for job failure notification you dont need to use it

    You've standard notification feature available in SQL Agent job properties for this

    Just set a operator in SQL Agent for that and then you can select the operator in the Notification tab of the job and configure it for jib failures

    You need to make sure database mail profile is setup with an account prior to these

    All of these are explained with screenshots in the link below

    https://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Mohsin_A_Khan Saturday, June 9, 2018 10:56 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 7:01 AM
    Friday, June 8, 2018 10:33 AM

All replies

  • sp_send_dbmail  isn't deprecated and you can use it, but the easier way is to use build-in Feature of Job notification: Notify an Operator of Job Status

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Mohsin_A_Khan Saturday, June 9, 2018 10:56 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 7:01 AM
    Friday, June 8, 2018 10:28 AM
  • sp_send_dbmail   is not deprecated and its not in the road map too yet for deprecation in any of the future releases.

    But even otherwise, for job failure notification you dont need to use it

    You've standard notification feature available in SQL Agent job properties for this

    Just set a operator in SQL Agent for that and then you can select the operator in the Notification tab of the job and configure it for jib failures

    You need to make sure database mail profile is setup with an account prior to these

    All of these are explained with screenshots in the link below

    https://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Mohsin_A_Khan Saturday, June 9, 2018 10:56 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 7:01 AM
    Friday, June 8, 2018 10:33 AM