Alex Duggleby's Palace of Words

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.