Database Mail does not work from SQL Agent Job

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

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet