Database Mail - Failed to notify "XYZ" via email.
Database Mail - Failed to notify "XYZ" via email.
(OP)
Hello,
I have a job that is supposed to send an email notification to an operator on failure. The job completes with SUCCESS, however even when it does not the email is not sent and there is the
Thank you for your help.
I have a job that is supposed to send an email notification to an operator on failure. The job completes with SUCCESS, however even when it does not the email is not sent and there is the
message when I look at the job history and click on the line item, without expanding it. Database Mail is properly configured on the server, a profile is setup accordingly and the operator is also configured. Test emails are sent fine. I am even able to send emails using the send mail stored procedure from within other SPs and jobs. I have scoured the Internet for solutions and all that is suggested is already in place. What am I missing? Is there something I am not aware of?Quote:
NOTE: Failed to notify "XYZ" via email.
Thank you for your help.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: Database Mail - Failed to notify "XYZ" via email.
What time does the mail message get sent? Is the exchange server down at that time? Is the network down at that time? If it is failing at the same time every day/night...set up a test job that will send an email to that operator and you at the time it has been failing. If it works and the real job fails, you know it is an issue with the way the email notification is set up in the real job. If the test job fails to send the email, you know it is an issue with exchange or the network.
-SQLBill
The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?
RE: Database Mail - Failed to notify "XYZ" via email.
CODE -->
In sysjobstepslogs, the message Job 'TestFailureEmail' : Step 1, 'Bad query' : Began Executing 2014-07-17 12:05:18 is recorded. Not very informative. A profiler trace does not reveal a call to the email procedure neither...Maybe it does not get that far.
What's curious is the test email is delivered. It is sent from the same profile and to the same user as in the job's Notifications...
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: Database Mail - Failed to notify "XYZ" via email.
Check the operator to make sure the name is correct (no typo or spaces).
Create a second step that fires upon success. In the step have it send an email to the operator.
sp_send_dbmail <etc>
That will give another check as to whether the issue is with the job itself or the operator setting.
Was the job created before the operator was created? If so, I've seen that cause a problem....you can try scripting the job, dropping it, and recreating it via the script.
-SQLBill
The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?
RE: Database Mail - Failed to notify "XYZ" via email.
Thanks again for your time.
Yes, I am using the Notifications tab. The operator can receives emails Mon-Fri, 8AM-9PM and 8AM-6PM on week-ends. He was setup very long ago; the job was created today.
I added a step to fire the email upon successful completion of the previous one, using sp_send_dbmail (I am on SS 2005). The email is successfully sent while the notification still fails. The operator name is not misspelled as it's selected from a drop-down.
However, the same job runs fine on my SS 2012 box...I will try to create a new operator tomorrow.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: Database Mail - Failed to notify "XYZ" via email.
The operator was created long before the job. However, I created a new operator and it works. The email is delivered when the job fails. It must be something along the lines of what you stated: a disconnect between the job and the old operator. So it works now. Thanks a bunch.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).