2

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?

2

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

| improve this answer | |
  • 1
    I know of the GUI approach, but was wanting to do it all in a script so I could quickly jump from server to server. Your script did the trick. – Dave Johnson Aug 7 '14 at 16:34
  • I was trying to point out that you can determine many scripts by using the "Script" button at the top of most dialogs in SQL Server Management Studio. But, either way you're welcome for the answer! – Max Vernon Aug 7 '14 at 16:37
  • Ha, missed the script part. Thanks for the tip :) – Dave Johnson Aug 7 '14 at 16:43
  • 1
    My pleasure. It's an easily overlooked, but handy-as-hell feature. I've used it many times to learn how SQL Server Management Studio does things internally. – Max Vernon Aug 7 '14 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.