Database mail does not work from Sql Server Agent Job after upgrade to SP1

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?

I had the exact same problem with the same error on 2005. To fix it, I did the following:

1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).

2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".

|||

I am still having issues after working through all these steps. Any other suggestions.

In general I seem to be having a lot of issues with the 64 Bit on 2005.

|||

I'm having the exact same issue.

I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:

"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"

Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.

|||

I just started working on the mail on my test server and I have the same problem.

I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.

|||

Ok I found another test I could run to see if I have it configured correctly.

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'User@.Place.com',

@.subject=N'just another mail test.',

@.body=N'just another mail test.',

@.profile_name = 'EMail';

When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.

|||

Ok it looks like I might have solve it for my self. Follow this link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1