SQL Server Agent Database Mail – How to Enable Using T-SQL

database-mailsql serversql-server-agentt-sql

I have several new servers that need Database Mail configured on them. I found the TechNet article describing how to add the profile and account via procedure calls, but is there a way to enable database mail and select the profile for the SQL Agent in the same manner?

Best Answer

The following T-SQL will enable Database Mail in SQL Server Agent:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', 
    N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', 
    N'REG_SZ', N'<profilename>'
GO

For future reference, you can easily determine this by expanding the "SQL Server Agent" node in SQL Server Management Studio's Object Explorer pane. Then right-click on "SQL Server Agent", click "Properties", select "Alert System", enter the appropriate details, and click on "Script":

enter image description here

Related Question