Problems with sending notification emails from SQL Server 2008

I have a problem setting up email notifications in SQL Server 2008.

I am using SQL Server 2008 to build a data cube. This process consists of several jobs, that are all scheduled to run each night at a specific time. To see whether a job was running without any failure, it is possible to set up email notification.

_{(source: smilinginthesun.de)}

_{(source: smilinginthesun.de)}

Unfortunately, no email is sent even though I think I have set up datab开发者_开发问答ase mail correctly.

All guides I can found just explains, how to set up database mail (eg. http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/), but thats it.

If I sent a mail to test functionality (via test email button or from T-SQL script) it works just fine and the email arrives. But when using the notification feature in a job, it doesn't even create a sysmail_mailitems or sysmail_log entry.

I have also send an email using sp_notify_operator, successfully. So the operator set up seems to work, too. (Thanks, Joe Stefanelli, for the hint.)

There is a great workaround written for SQL Server 2005, that works for me, too: http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/, but why isn't it possible to just use that build-in functionality?

Does anyone knows if there is anything else to do, to make that email notification task work properly? Thanks in advise.

I am late but I saw your post having the same problem.

The solution is to enable a mail profile for alerts.

For this:

1. Right click on "SQL Server Agent"
2. Then "Properties".
3. Then go to "Alert System" section.
4. Tick the "Enable mail profile" box.
5. Then "OK".
6. Restart the SQL Server Agent service.

To send email notifications from agent jobs in SQL Server Agent, the alarm system must be activated. We did that. After restarting the agent, the Database Mail system should be used. Unfortunately, this does not work. The agent was still trying to reach a Mapi profile (as in SQL 2000).

The following registry value wasn't set: "UseDatabaseMail"=dword:00000001
After setting it to 1, the database mail system was used.
You can do that in SQL server, directly:

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

I've tried what ZeuG. indicated and still didn't have success... then I restarted the SQL Agent. That's what made the job email notification work for me.

For me, SQL email worked fine when I right-clicked Database Mail and said Send Test Email. However, after setting up alerts for all severities and running RAISERROR (N'Test alert!', 20, 1) WITH LOG;, no email came through. The issue is the service account that SQL Server Agent runs under. Apparently, the virtual account NT Service\SQLSERVERAGENT (or whatever your default may be) does not have permissions to access objects on the network and this includes e-mail.

To fix, change the service to use a local account under the Users group, restart SQL Server agent, and then try running a command that throws an error like the RAISERROR statement above. You should now receive the email.