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 straightforward. 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 Database 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 where 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 those commands (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 on the right job under jobs in the SQL Server Agent), you have the tab “Notifications.” Here you activate Email, 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 running the job.