August 27, 2008 at 10:10 am
Hey all,
I'm not sure what to say about this one ... On all my 2005 servers, I run a database mail script as part of my admin update utility (synch'd dba database amongst all servers) and I setup operators for my jobs. They work across the board, except on a new instance I recently brought up. An instance on this same server works just fine ... but yet on this instance, nothing. sp_send_dbmail works just fine ... it's only on job alerting that I'm having an issue.
Nothing in sysmail_mailitems, sysmail_log. I don't understand why this one instance amongst my entire environment is not working.
Here is what I run on all my servers:
/*Enable broker*/
IF (SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb') = 0
BEGIN
ALTER DATABASE msdb
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
END
/*Find out if sp_configure options are enabled*/
DECLARE
@ShowAdvOptTINYINT
,@DBMailXPTINYINT
SELECT @ShowAdvOpt = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Show Advanced Options'
SELECT @DBMailXP = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Database Mail XPs'
IF @ShowAdvOpt = 0
BEGIN
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
END
IF @DBMailXP = 0
BEGIN
EXEC sp_configure 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
END
IF NOT EXISTS(SELECT name FROM msdb.dbo.sysmail_account WHERE name = 'Exchange')
BEGIN
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Exchange',
@email_address = 'removed',
@replyto_address = 'removed',
@display_name = 'SQLMONITOR',
@mailserver_name = 'removed'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLMONITOR'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLMONITOR',
@account_name = 'Exchange',
@sequence_number = 1
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLMONITOR',
@principal_name = 'public',
@is_default = 1;
END
/*Setup mail operator*/
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = 'SQLDBA')
BEGIN
EXEC msdb.dbo.sp_add_operator @name=N'SQLDBA',
@enabled=1,
@pager_days=0,
@email_address=N'removed'
END
/*Setup pager operator*/
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = 'SQLDBASupportText')
BEGIN
EXEC msdb.dbo.sp_add_operator @name=N'SQLDBASupportText',
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address=N'',
@pager_address=N'removed',
@netsend_address=N''
END
/*Enable operator alerting*/
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
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'SQLMONITOR'
August 27, 2008 at 11:34 am
DB Mail needs these three pre requisites:
Enabled in sql SAC
Service borker enabled in the msdb
SQL Server service account needs access to SMTP if it requires auth
But you've probably checked all of these....
August 27, 2008 at 12:08 pm
Yeah, the first two are taken care of in the above script and the account has access.
August 27, 2008 at 12:11 pm
If DBMail works everywhere but in the Agent Alerts, then I would check out their definitions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2008 at 2:42 pm
I had a problem similar to this once when I forgot to tell the agent to use the DB Mail profile to send alerts. Is it possible you haven't set that up?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2008 at 4:20 pm
Good point. Gus. It could be set on SQL Mail instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 8:21 am
GSquared (8/27/2008)
I had a problem similar to this once when I forgot to tell the agent to use the DB Mail profile to send alerts. Is it possible you haven't set that up?
Good thought, but no ... that is taken care of in the above script.
August 29, 2008 at 7:44 am
Adam Bean (8/28/2008)
GSquared (8/27/2008)
I had a problem similar to this once when I forgot to tell the agent to use the DB Mail profile to send alerts. Is it possible you haven't set that up?Good thought, but no ... that is taken care of in the above script.
I must be missing something, because I don't see that in the script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 29, 2008 at 10:40 am
You need to restart the agent for it to take effect. If it still isn't working then try removing dbmail from the agent, restarting, adding it back and restarting again.
September 2, 2008 at 11:27 am
Whoops, thought you were talking about something else there G ... where exactly can you set this? As stated, this is the script I've run on 30+ servers without issue ... and now it's a problem. The crazy thing is I just brought up another new server today, and same problem.
September 2, 2008 at 11:37 am
Right-click on SQL Server Agent in Management Studio, select Properties, go to Alert System, and make sure Enable Mail Profile is checked. Also make sure it's got Database Mail as the mail system and is using a valid profile.
If it's all set up correctly, click Test and make sure the e-mail goes through.
You might already have this. Your script does have the registry key modifications for it (which I missed when I first read it), but there are other parts as well. Just double-check to make sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 11:45 am
GSquared (9/2/2008)
Right-click on SQL Server Agent in Management Studio, select Properties, go to Alert System, and make sure Enable Mail Profile is checked. Also make sure it's got Database Mail as the mail system and is using a valid profile.
Yeah those are all set, the registry writes take care of that page.
I think I figured out my problem though ... has to do in the order in which I was pushing out my admin scripts. I re pushed my objects and the original server in question is now working. I pushed them again and now the new server is working. So I think I'm all set at this point.
Thanks!
September 2, 2008 at 1:16 pm
Cool. Glad you got it up and running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply