Database Mail - Failed to notify "XYZ" via email.

[TheBugSlayer]

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

NOTE: Failed to notify "XYZ" via email.

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?

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).

 

[SQLBill]

Sometimes the history doesn't give the full message. Expand the history and look at the message for the actual step that sends the mail message. Also, I suggest going to that step, then to the Advanced tab and set Log To Table. you might get more detailed information that way.

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

 

[TheBugSlayer]

Thank you SQLBill. I have a simple test job that runs a SELECT 0 statement (originally SELECT 1/0 but I changed it just so see the behavior when the job succeeds), that's all. In Notifications I set up E-mail operator when job succeeds. The job succeeds, email fails but that failure is not reported as an error but rather a note at the bottom of the job execution report. Here is the full text:

Date		7/16/2014 11:17:39 AM
Log		Job History (TestFailureEmail)

Step ID		
Server		SQL1
Job Name		TestFailureEmail
Step Name		
Duration		00:00:00
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
The job succeeded.  The Job was invoked by User Network\Administrator.  
The last step to run was step 1 (Bad query).  NOTE: Failed to notify 'Operator Name' via email.

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).

 

[SQLBill]

I'm guessing you are using the Notification Tab in the job to send the email. Is there a window set for the operator to be notified?

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

 

[TheBugSlayer]

SQLBill,

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).

 

[TheBugSlayer]

SQLBill,

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).