May 1, 2009 at 9:58 am
Hey all,
I do not yet know why, but I have a new instance in which my operator groups are not being notified on job failure. I have deployed my normal scripts I use to setup and configure database mail and the operators just like I do on all my instances.
- msdb.dbo.sysmail_allitems has no record of the agent notifications
- Have used both a local and domain admin as the agent account
- Can successfully use sp_send_dbmail
- Can successfully use database mail test
My script:
-
/*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*/
IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Show Advanced Options') = 0
BEGIN
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
END
IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Database Mail XPs') = 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 = 'XXXX',
@replyto_address = 'XXXX',
@display_name = 'SQLMONITOR',
@mailserver_name = 'XXXX'
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;
-
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'
Any thoughts?
May 4, 2009 at 6:10 am
I can in fact notify operators just fine:
EXECUTE msdb.dbo.sp_notify_operator @name=N'operator name',@body=N'test message'
Still not sure what my issue is here ... All other instances working just fine. Just wish I had something else to work off ... no errors, nothing in the mail tables, notta. Only thing I have is in my job history "Failed to notify 'SQLDBA' via email.".
May 4, 2009 at 6:49 am
Alrighty, well ... looks like restarting the agent resolved it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply