Database Mail Problems / Job Notifications...

I've read an extensive bit about this problem in several forums and so far none of the information has helped me to determine if there is a bug in SQL 2005 x64 regardless of version or SP or if I just don't know how to do this correctly.

I can send a test email from db mail but when a job fails it does not send an email to an operator. I have monitored the IIS logs to see if it is trying to send and it is not. Database mail is enabled in Surface AC and in SQL Agent [one odd thing is that the 'test' button is only available on the SQL Agent Alerts page if I use SQL Mail]. No specific error happens (in the mail log, sql agent log, or application event log) when I try to send mail from a failed job. Mail accounts and credentials are valid. Please note that in the steps listed below, I have restarted the SQL Agent Service each time I make a change.

I've tried deleting and recreating the operators (they are enabled). I've tried using a mail server that uses anonymous authentication as well (I changed this back to one that uses basic authentication). The profiles were made default and public. (Though this shouldn't ever have to be done, as the account is a sysadmin) the SQLServer2005SQLAgentUser was explicitly added to the msdb DatabaseMail user role.

This query works, so I know that the job is not trying to execute the sp:

USE msdb ;

GO

EXEC dbo.sp_notify_operator

@.profile_name = N'staging monitor',

@.name = N'Charles Evans',

@.subject = N'Test Notification',

@.body = N'Successfully emailed profile.' ;

GO

This is happening in SQL 2005 x64 EE and x64 DE SP1 and SP2 respectively. Any thoughts on what else this could be? The same setup works fine in SQL 2005 SE x86 SP1.

Check the following article...

How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

|||So the only way is to use SQL Mail, instead of database mail?|||

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions.

Please read the article... you can setup mail using SMTP where SQL mail uses MAPI ...

|||

In SP0 this is true - according to the article:

"In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems."

I've made this work in our production system now with SP-1 EE x64, there was a permissions issue. However, things are not working in our staging environment SP-2 DE x64 with the same setup and account settings/permissions.

I'll keep posted if I figure out why not in staging.