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

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

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:

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

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

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

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