[264] An attempt was made to send an email when no email session has been established

Hello,

I am running SQL Server 2005 SP3 and I have a problem with receiving email notifications.
I setup a Database profile and when I send out a test email then I receive it. I then enabled the mail profile in the SQL Server Agent  properties but in there the test button is greyed out.

After all of this I created an SQL JOB and configured to receive notifications if the jb fails. Well the emails are never being send out. When I check the SQL Server Agent logs then I find the errors below with time stamps when the email should be send out:
=======================================================================
Date            4/25/2010 8:03:24 PM
Log            SQL Agent (Current - 4/25/2010 8:03:00 PM)

Message
[264] An attempt was made to send an email when no email session has been established
=======================================================================

I looked here in the forum but I was not able to find any useable solution.

Can anyone help please?

Thank you
Mc2102
Mc2102Asked:
Who is Participating?
 
bchoorCommented:
TRy this:

1. Uncheck "Enable mail profile" on SQL agent properties/Alert system
2. Stop SQL Agent
3. Start SQL Agent
4. Enable database mail in the properties
5. Stop SQL Agent
6. Start SQL Agent

HTH
0
 
Mc2102Author Commented:
Hello bchoor,

I just followed your procedure.  Now when I execute the job manually and it fails it should send me a notification as well right?
0
 
bchoorCommented:
It should - Is it giving any errors? Can you do a try catch exception around the send mail command and insert the exception_message into an exception table? that way we can see what issue is occurring - when you do it manually.

maybe:

CREATE EXCPTABLE.......;

BEGIN TRY
  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'aaa', '@recipients = 'someone@co.com', @body = 'Job did not complete', @subject = 'Job did not complete'
END TRY
BEGIN CATCH
  INSERT INTO [EXCPTABLE]
  (Msg)
  VALUES(ERROR_MESSAGE())
END CATCH
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
bchoorCommented:
If the emails are coming from SQL SErver Agent (i.e. notifications per job status) , then it won't unless you force the job to run through SQL SErver agent.
0
 
Mc2102Author Commented:
Bchoor:

I just ran my job again and made it fail in intention and everything worked. The email notification was send out.

The fix is a little odd but it worked. So thank you very much.
0
 
Mc2102Author Commented:
Worked! Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.