I struggled with this for most of the day yesterday. I searched and searched, googled and googled, with no workable solution. Just about every thread and blog I read recommended restarting the SQL Server Agent and this seemed to work for most people, but alas, would not work for me. Today, I came in with some fresh coffee and fresh perspective and figured it out.
Using SS2K8, Windows Server 2008, 32x, everything lives on one machine.
Sending a test email from Database mail worked.
Message from Job History:
"The job succeeded. The Job was invoked by User...bla bla bla...NOTE: Failed to notify 'me' via email."
From Agent Error Log:
" An attempt was made to send an email when no email session has been established."
Tried the following Solutions:
In SQL Server Agent Properties >> Alert System >> checked Enabled mail profile.
In Surface Area Configuration (which has been moved to Facets in SS2K8, btw) >> DatabaseMailEnabled = True.
Deleted and recreated both mail profile and operator.
Restarted Server Agent like a bazillion times.
The ultimate solution (I can tell you are on the edge of your seat, nice desk chair by the way, no, really):
Under Database Mail Configuration >> SMTP Authentication >> changed from Basic authentication to Anonymous authentication >>restarted Server Agent >>crossed fingers for good luck.
No, seriously, that was it. Don't ask me why it worked, but it worked. Hopes this helps someone.