April 14, 2008 at 5:46 am
Hello,
I hope somebody can help, I am having a go at getting a maintenace task sorted out, I've gone ahead and done that, but wanted the report e-mailed to me. So I went to the SAC and enabled database mail, made a profile, added an account and configured the SMTP info that's needed.
When I now go to the Maintainence plan and bring up the reprting and logging I get a box appear when I tick the 'Send report to an e-mail recipient' that says 'There are no operators with email addresses defind on this server', fair enough, but having looked around on the web, I get a lot of hints and tips on the creation of the task and setting up database mail, but cant seem to find anything on this particular error/notice. Would someone be kind enough to point me in the right direction, please?
Thank you in advance for your time,
Kind Regards,
D.
April 14, 2008 at 6:12 am
It looks like you haven't defined an Operator yet. Look in SSMS under 'SQL Server Agent' and 'Operators'. You probably have to use a group mail to notify multiple persons because as an alert in Agent-Job only allows one operator. (But this can differ while you are defining it from the maintenance task)
Hans
April 14, 2008 at 6:19 am
Script to add operator and add operator to alerts. You will need to alter the operator name and email address. You will also need to go into SQL Server Agent Properties and under Alert System. Click on Enable mail profile and point to the Mail profile created in the script.
USE [msdb]
GO
DECLARE @OpName VARCHAR (50),
@OpID INT,
@Count INT,
@Recs INT
SET @OpName = 'DBA'
/*Check if operator exists*/
SELECT @Recs = Count(*)
FROM [msdb].[dbo].[sysoperators]
WHERE [name] = @OpName
IF @Recs = 0
BEGIN
EXEC msdb.dbo.sp_add_operator @name= @OpName,
@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=N'youremail@whatever.com,
@category_name=N'[Uncategorized]'
END
/*Clear record count*/
SET @Recs = 0
/*Get Operators ID */
SELECT @OpID = ID
FROM sysoperators
WHERE name = @OpName
/*Add Job Alert Notification*/
UPDATE sysjobs
SET [notify_level_email] = 2,
[notify_level_eventlog] = 0,
[notify_email_operator_id] = @OpID
/*Add Alert notification*/
UPDATE sysalerts
SET has_notification = 1
SET @Count = (SELECT MIN(id) FROM sysalerts)
WHILE @Count <= (SELECT MAX(id) FROM sysalerts)
BEGIN
/*Check Alert exists*/
SELECT @Recs = Count(*)
FROM sysnotifications
WHERE alert_id = @Count
/*If alert doesn't exist add*/
IF @Recs = 0
INSERT INTO [msdb].[dbo].[sysnotifications]
([alert_id]
,[operator_id]
,[notification_method])
VALUES
(@Count
,@OpID
,1)
SET @Count = @Count +1
END
GO
April 14, 2008 at 7:56 am
Thank you very much, guys, I will try it all out.
Kind Regards,
Paul.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply