locked
troubleshoot SQL server email alert RRS feed

  • Question

  • want to send email when sql server job fails.

    1 sql server profile

    2 sql server operator : operator A and operator B

    I want some failed sql job alerts to operator A and other failed sql job alerts to operator B

    But all failed sql job alerts emailed  to operator A even "job properties--notification--Email: operator B when job fails"

    What's wrong here?

    Wednesday, December 21, 2016 4:17 PM

Answers

  • Hi,

    Right click on SQL Server Agent -->Properties -->Alert System -->Enable mail profile 

     

    Restart the SQL Server Agent service.


    SELECT  * FROM [msdb].[dbo].[sysmail_sentitems]

    SELECT  * FROM [msdb].[dbo].[sysmail_faileditems]

    SELECT  * FROM [msdb].[dbo].[sysmail_allitems]


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Thursday, December 22, 2016 5:01 AM

  • But all failed sql job alerts emailed  to operator A even "job properties--notification--Email: operator B when job fails"

    What's wrong here?

    Hi, So Email are working as I can see from the post that they are being sent to Operator A even you have mentioned it to sent to Operator B for some of the jobs.

    Can you verify properties of Operator B and check the correct email are mentioned? Go to SQL Server Agent>Operators>Right Click Operator B and check General Tab.

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, December 22, 2016 5:26 AM
  • If an email is sent by Database Mail when a job fails, then it means that you have already configured Database Mail and enabled the mail profile.

    I suggest you to take a look at the related system tables like:

    select * from msdb.dbo.sysmail_mailitems order by send_request_date desc

    and see if the recipient is the expected? Other than that, you can check your operators if they are configured for the correct recipient or not?


    Microsoft SQL Server Consultant | http://ekremonsoy.blogspot.com (Turkish) | http://www.ekremonsoy.com (Turkish) | @EkremOnsoy

    Monday, December 26, 2016 8:29 AM
    Answerer
  • Hi bestrongself,

    you have create two opertator.

    select job ---> property-->Notifications-->Click on email--> select profile and and when job is failed.

    Any thing, Let me know.Same way you have do for other jobs.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Tuesday, December 27, 2016 5:28 AM

All replies

  • It sounds like you have setup everything correctly.

    Did you check the mail log?

    Wednesday, December 21, 2016 4:23 PM
  • I check Log File viewer-->Database Mail -->Database Mail log. 

    0 records there. 

    Any help?

    Wednesday, December 21, 2016 5:38 PM
  • Can you send out mail using sp_send_dbmail?
    Wednesday, December 21, 2016 6:19 PM
  • Hi,

    Right click on SQL Server Agent -->Properties -->Alert System -->Enable mail profile 

     

    Restart the SQL Server Agent service.


    SELECT  * FROM [msdb].[dbo].[sysmail_sentitems]

    SELECT  * FROM [msdb].[dbo].[sysmail_faileditems]

    SELECT  * FROM [msdb].[dbo].[sysmail_allitems]


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Thursday, December 22, 2016 5:01 AM

  • But all failed sql job alerts emailed  to operator A even "job properties--notification--Email: operator B when job fails"

    What's wrong here?

    Hi, So Email are working as I can see from the post that they are being sent to Operator A even you have mentioned it to sent to Operator B for some of the jobs.

    Can you verify properties of Operator B and check the correct email are mentioned? Go to SQL Server Agent>Operators>Right Click Operator B and check General Tab.

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, December 22, 2016 5:26 AM
  • If an email is sent by Database Mail when a job fails, then it means that you have already configured Database Mail and enabled the mail profile.

    I suggest you to take a look at the related system tables like:

    select * from msdb.dbo.sysmail_mailitems order by send_request_date desc

    and see if the recipient is the expected? Other than that, you can check your operators if they are configured for the correct recipient or not?


    Microsoft SQL Server Consultant | http://ekremonsoy.blogspot.com (Turkish) | http://www.ekremonsoy.com (Turkish) | @EkremOnsoy

    Monday, December 26, 2016 8:29 AM
    Answerer
  • Hi bestrongself,

    you have create two opertator.

    select job ---> property-->Notifications-->Click on email--> select profile and and when job is failed.

    Any thing, Let me know.Same way you have do for other jobs.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Tuesday, December 27, 2016 5:28 AM