“SQL Server 2008 Agent will not accept DatabaseMail (bug in feb ctp?)” or “How to activate DatabaseMail using stored procedures/regedit”

I wanted to set up some error notification for a SQL Server Agent job this morning on our SQL Server 2008 (February CTP 10.0.1300) installation. It’s fairly straight forward. First you set up database mail using the configuration wizard. Next you set up your operators. Now comes the interesting part that I had to troubleshoot.

In SSMS (SQL Server Management Studio) open the properties tab of the SQL Server-Agent. There you will find what is most probably called “Notification system” or “Warning system” (I’m sorry for that. Somebody installed a German server *doh* Why are servers translated? Come on, it’s not necessary if you ask me). Activate the mail profile checkbox, select datbase mail as the mail system and then the profile you just selected. Ok the dialog. Then right-click on the agent again and restart it. Re-open the properties dialog and you may find that the mail system has jumped back to SQLMail. If not, continue reading below, maybe you are running a version were this was fixed. However I tried generating a script to see what the dialog is doing behind the scenes when I activate database mail. (By the way, one of my favorite features in the newer Microsoft tools!).

The script read:


USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_profile=N'Default'
GO

This sounded a bit strange, because there was no reference to the actual activation of database mail. Only the profile was set. Thanks to this TechNet article I found the commands required to activate database mail.


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

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default'

Execute that (or make the changes in regedit yourself), restart the sql server agent and voila database mail is activated.

Next up is the easy part. On your job properties (right-click ont he right job under jobs in the SQL Server Agent) you have the tab “Notifications”. Here you activate E-Mail, select an operator, select the cause and you are ready to go. For testing I would recommend selecting ‘At the end of a run’ (again, sorry for a possible wrong translation) and then run the job.

Advertisements

2 thoughts on ““SQL Server 2008 Agent will not accept DatabaseMail (bug in feb ctp?)” or “How to activate DatabaseMail using stored procedures/regedit”

  1. Thanks a lot. Learning for 70-432 and only here I found the solution – non of the MS Press books covered this issue.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s