We have a job scheduled to notify an operator on failure. However, the following is written to the error logs each time this is attempted:
 An Attempt was made to send an email when no email session has been established
To troubleshoot I have re-created the issue by setting up a test job that executes a T-SQL statement, notifying the following operator when the job succeeds (i.e. regardless of outcome).
I recently built an x64 cluster and applied sp2 "out of the box" before I customized anything.
This client prefers using a standardized SQL script to enable mail, and I used that script. As I was setting things up, one thing that I always check is that after setting up SQL Mail that the SQL Agent properties show mail enabled, and this time it was not enabled!
The bad news is that I still don't have mail working on this cluster, and can't figure out why... I'm sill looking for the reason that mail isn't working (and watching the SQL and event logs like a hawk), but I'm seriously thinking of asking for down time and seting up the mail configuration again manually to see if that will correct the problem.
George: If you have set database mail up manually, take a minnute to check the SQL Agent properties. I think that is the most likely culprit under those conditions. If you do find an answer, PLEASE let me know because it might help me solve my problem (at least the one with database mail anyway)!
As my understanding of the history goes, database mail has worked in the past and only recently has it had problems - so I don't think our issues are related... Also, this is not a 64-bit install, and on my Google travels I noticed some people had issues with 64 and not x86
Just out of interest Pat - can you execute sp_send_dbmail? Does anything come up in the logs at all - start/stop activity but no mail able to send?
Sadly I didn't set up this server (I never get to do any of that stuff really!) which is partly why it's never been my problem
If I come across anything which might be of use to you, I'll let you know.
Interestingly enough, any user-initiated email works on these servers, but service initiated emails fail. I can call any mail related stored procedure and they work just fine. It is just services like SQL Agent that are giving me problems.