locked
SQL Agent Job Notification - Database Mail problem RRS feed

  • Question

  • Hi
    I'm a little stumped. Have setup database mail on SQL Server 2008 (SP1 x64) and its working fine except when its supposed to send out a SQL Agent job notification.
    I've enabled database mail on SQL Agent and have specified a profile, but when it tries to send an email on agent job completion it errors with "NOTE: Failed to notify '<operator>' via email."
    Can anybody help?
    Thanks

    Wednesday, February 24, 2010 10:01 AM

All replies

  • Hi,

    Have you restarted the SQL Agent after adding the Mail profile to the Alert System property option? If not please restart the agent. This may solve the issue.

    If you get the error please reply with the error details.

    Thanks 
    • Proposed as answer by kenmartin Friday, April 27, 2012 12:10 PM
    Wednesday, February 24, 2010 11:41 AM
  • Adam,

    Do you have any type of software such as Mcafee or any firewalls/rule blocking that would stop transmission on that server? Have you tested sending a mail from the agent via the test button? Are there any mispellings in the e-mail address? What is in the sql agent logs (about database mail)? Try restarting SQL Agent, I've noticed sometimes dealing with mail profiles it can get confused and error out.

    Hope this helps,
    Sean
    Wednesday, February 24, 2010 11:41 AM
    Answerer
  • Hi to both

    Thanks for the replies.

    Yup I restarted SQL Agent after setting it up. I even setup the mail profile again (and restarted the agent) to no avail.

    Definitely no firewalls/AV software getting in the way as I can run the following and send a test message fine:

    use msbd
    go
    exec sp_send_dbmail @profile_name='idc091d02iics - Mail',
    @recipients='email@email.com',
    @subject='test',
    @body='test message'

    Its only SQL Agent job notifications that fail to send the mail.

    BTW theres no Test button when I select Database Mail on SQL Agent - test is greyed out unless I select SQL Mail.

    Thanks again
    Wednesday, February 24, 2010 1:40 PM
  • Adam,

    Is this a notification that the job has failed/succeeded? Are the operators setup correctly and set to receive e-mail alerts in the job? Is the job set to quit without notification on step failure?

    Edit: If you right-click on the database mail option in SSMS under management, you should see a test e-mail option, my fault on the direction not being correct.

    -Sean
    Wednesday, February 24, 2010 6:35 PM
    Answerer
  • Go to configure database mail and look at the profile that the SQL Agent is using. Is it set to public and set as the default profile?


    Joie Andrew "Since 1982"
    Wednesday, February 24, 2010 10:56 PM
  • Hi there

    Thanks for the replies.

    This is notification that a job has failed/succeeded thats correct. So I'm using the Notification tabs on the Agent job properties.

    Right clicking Database Mail > Send Test Email works fine. I get the email.

    SQL Agent is using the default public mail profile. (There are no private profiles configured).

    Thanks
    Thursday, February 25, 2010 1:16 PM
  • Hi. I have two question for you? 
    Are you using a domain account for Sql Server agent?
    In sql server agent properties, Alert System, Mail Session, if you press "TEST" button, you get the email?


    Thursday, February 25, 2010 5:14 PM
  • Hi

    Yes its a domain account.

    Will test that tomorrow and let you know.

    Cheers :)
    Thursday, February 25, 2010 9:11 PM
  • Hi

    Test button is greyed out when Database Mail is the selected Mail System in SQL Agent Properties > Alert System > Mail Session

    The button only comes to life if I select SQL Mail. But SQL mail isn't configured so this would be no use..
    Friday, February 26, 2010 8:26 AM
  • Adam can you send me the screenshots to my mail account?

    amiranda@gpilatam.com

    Thanks.

    Friday, February 26, 2010 7:31 PM
  • How did you get this resolved?
    Friday, October 8, 2010 5:54 PM
  • Did anyone figure this out?  I have the same problem.
    Saturday, December 18, 2010 3:44 AM
  •  Hi Webdawg, 

      As suggested above, try these steps.

     1. Try  test email first, this can be by done (right click on database mail under mangement folder). prior to that you should already have a profile configured to send the emails and make sure you got the right SMTP server and  do not block port 25.

    2.If test is succeeds, enable mail option under sql server agent properties and select 'database mail' and corresponding profile.Add the reciepents email to the operators under sql server agent(check the enabled option here). Restart the SQL sever here.

    3. Create your sql job and in the notifcations section you should see email option and should have the operator email you configured above.

    Try it out. let us know. Hope it helps.

    If the test succeeds and still you did not get the email from sql job, check the database mail log and check whether it is being queued. let us know. 


    Please mark as 'Answer', if the solution solves your problem.
    Saturday, December 18, 2010 5:19 AM
  • 1) Have the mail profile set and test it using sp_send_dbmail.

    2) In the agent properties >> Alert System >> check the Enable mail profile >> Select database mail in Mail system >> Select the mail profile >> no need to restart agent .

    Check the errors if any in the agent logs or database mail history .

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by ClockWatcher Wednesday, December 14, 2011 3:14 AM
    Saturday, December 18, 2010 1:19 PM
  • Hi Adam,

    Did you solve this issue? As I am facing the same issue. sending test mail works fine.

    1. I have enabled mail profile in my sql server agent's properties and restarted it.

    2. checked my Profile is enabled and set as default.

    3. checked error log and found out it says "NOTE: Failed to notify 'operator_name' via email."

    Any suggestions?

    Thanks.

    PS. I am using SQL server 2008 R2.

     

    Wednesday, December 22, 2010 4:18 PM
  • Ensure Service Broker is enabled on the MSDB database.  Give this article a look for more info: http://msdn.microsoft.com/en-us/library/ms189959.aspx

     

    • Edited by jmeis-w Thursday, January 20, 2011 10:12 PM spelling correction
    Thursday, January 20, 2011 10:11 PM
  • Have you added the destination email address into the Operators section of the SQL Server Agent object explorer? 
    Monday, October 3, 2011 3:25 PM
  • Look at step two here. This is the solution. And as he says, no need to restart the agent.
    Wednesday, December 14, 2011 4:08 AM
  • Yes I had the same problem... and restarting the SQL Agent service fixed it.  Thanks!

     

    Tuesday, January 17, 2012 10:14 PM
  • Hi,

    I had the same problem. I just fixed it now. What missing is the operator. You just need to create a operator before using Database mail for Notification.

    Good luck!

    • Proposed as answer by ktrock Tuesday, December 31, 2013 9:17 PM
    Wednesday, May 23, 2012 10:21 PM
  • I had this same issue with SQL 2008 R2 SP1 and after I restarted the SQL Server Agent e-mail notifications started working.

    Regards,
    Babak

    Tuesday, June 12, 2012 4:59 AM
  • For me it was creating an operator to email to. Kudos to ninadba for isolating this in their response. In fact, I'm able to email from an agent job without having 'Enable mail profile' checked. This feels like a workaround but at least I can send email from a job.

    Ken

    Tuesday, December 31, 2013 9:22 PM