September 26, 2017 at 2:37 pm
We have moved our DBs over to a new SQL (2014 SP2) box as well as scripted out all the agent jobs and transferred them over.
I have been into DB mail and set that up with a profile/account and sent a test email with no problem.
We have code that uses sp_send_dbmail and that works fine too.
Operators are set up with the correct email addresses.
We use SQL Sentry and this sends us emailed alerts as and when required for the new server.
The thing is that I will get a daily email (spawned from SQl Sentry) to warn me that "Database Mail is not enabled for agentnotifications. Cannot send e-mail to xxx"
Have I missed a step in enabling something in SSMS. I just want to check that I have done everything there before I contact SQL Sentry to ask why I get these emails.
September 26, 2017 at 2:45 pm
Jay@Work - Tuesday, September 26, 2017 2:37 PMWe have moved our DBs over to a new SQL (2014 SP2) box as well as scripted out all the agent jobs and transferred them over.
I have been into DB mail and set that up with a profile/account and sent a test email with no problem.
We have code that uses sp_send_dbmail and that works fine too.
Operators are set up with the correct email addresses.
We use SQL Sentry and this sends us emailed alerts as and when required for the new server.
The thing is that I will get a daily email (spawned from SQl Sentry) to warn me that "Database Mail is not enabled for agentnotifications. Cannot send e-mail to xxx"Have I missed a step in enabling something in SSMS. I just want to check that I have done everything there before I contact SQL Sentry to ask why I get these emails.
Right click SQL Server Agent in SSMS & get properties. Go to Alert System and ensure that the 'mail session' stuff is enabled and configured.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 26, 2017 at 2:51 pm
Perfect thanks. Now I see the propereties window I remember that I have forgotten this step with every version switch since SQL 2000 🙂
September 27, 2017 at 5:54 am
Jay@Work - Tuesday, September 26, 2017 2:51 PMPerfect thanks. Now I see the propereties window I remember that I have forgotten this step with every version switch since SQL 2000 🙂
I agree, very easy to forget this one.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 14, 2020 at 2:23 pm
Hero! Just forgot that step. Thanks!
May 14, 2020 at 7:44 pm
I found that this was something I would forget every time I setup a new server - so I created a script to take care of both database mail and setting up the agent.
Declare @serverName sysname = upper(@@servername)
, @emailAccount nvarchar(128) = '<profile email account>'
, @profileName nvarchar(128) = '<profile name>'
, @profileDescription nvarchar(256) = '<profile description>'
, @accountName nvarchar(128) = '<account name>'
, @accountDescription nvarchar(256) = '<account description>';
--==== Create a Database Mail profile
Execute msdb.dbo.sysmail_add_profile_sp
@profile_name = @profileName
, @description = @profileDescription;
--==== Create a Database Mail account
Execute msdb.dbo.sysmail_add_account_sp
@account_name = @accountName
, @description = @accountDescription
, @email_address = @emailAccount
, @replyto_address = @emailAccount
, @display_name = @serverName
, @mailserver_name = '<smtp mail server>';
--==== Add the account to the profile
Execute msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profileName
, @account_name = @accountName
, @sequence_number = 1;
--==== Grant access to the profile to all users in the msdb database
Execute msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @profileName
, @principal_name = 'public'
, @is_default = 1;
--==== Create an Operator
Execute msdb.dbo.sp_add_operator
@name = N'Database Administration'
, @enabled = 1
, @weekday_pager_start_time = 90000
, @weekday_pager_end_time = 180000
, @saturday_pager_start_time = 90000
, @saturday_pager_end_time = 180000
, @sunday_pager_start_time = 90000
, @sunday_pager_end_time = 180000
, @pager_days = 0
, @email_address = @emailAccount
, @category_name = N'[Uncategorized]';
--==== Enable Database Mail
Execute sp_configure 'show advanced options', 1; Reconfigure With Override;
Execute sp_configure 'Database Mail XPs', 1; Reconfigure With Override;
--==== Setup Agent Alert System
Use msdb;
Go
Execute master.dbo.sp_MSsetalertinfo
@failsafeoperator = N'Database Administration'
, @notificationmethod = 3;
Go
Executemsdb.dbo.sp_set_sqlagent_properties
@email_save_in_sent_folder = 1;
Go
Execute master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'UseDatabaseMail'
, N'REG_DWORD'
, 1;
Go
Execute master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'DatabaseMailProfile'
, N'REG_SZ'
, N'Database Administration';
Go
This makes sure I have a default public profile created - as well as enabling the agent with that profile.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply