Mc2102
asked on
[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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
ASKER
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.
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.
ASKER
Worked! Thank you.
ASKER
I just followed your procedure. Now when I execute the job manually and it fails it should send me a notification as well right?