Maintenance plan, notify operator, how to get content for message

I would like to add a notify operator task to my maintenance plan. I'd like to be able to send the contents of my maintenance plan execution logs. When I go to configure the notify operator task, all it seems I can do is put something in the message body. I don't see much purpose in that. How would I know if things ran OK or not?
LVL 1
jmarkfoleyAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Set "database maintenance" Database mail to be the Default profile and try it which should work..

http://www.tek-tips.com/viewthread.cfm?qid=1560515&page=10
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> all it seems I can do is put something in the message body. I don't see much purpose in that

You can have several alerts configured in your SQL Server..
And in that case, you can specify some subject and message to make it more clear on where this alert to operator is being triggered.

Hope this clarifies..
0
 
jmarkfoleyAuthor Commented:
> You can have several alerts configured in your SQL Server..
> And in that case, you can specify some subject and message to make it more > clear on where this alert to operator is being triggered.

Hmmm, you say this as if I already know what to do! What do you mean I can "have several alerts configured in [my] SQL Server"? How do I set that up?

I see where I can create new alerts in SQL Server Agents > Alerts, but how to I tie this to a maintenance plan notify operator task.

Also, what I'm most interesting in doing is sending the most recent  Maintenance Plan_Subplan_1_yyyymmddhhmmss.txt log file, but I don't see any way of doing that.

Thots?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> You can have several alerts configured in your SQL Server.

Typo here..
It should have read several operators defined in a server.

Once you have successfully created a operator, those operators would be visible over here in the Notify Operator task so that you can select the required operators..

You would be able to link an operator alone to this Notify Operator task and not alerts..
If you want to send an email in case of any job failures, then create an agent job without any schedule to send email to the required email ids and just call that Agent job using Execute SQL Server Agent Job Task..
Note: You can attach files using database Email that can be configured in Agent job.

Regret if I have confused you earlier..
0
 
jmarkfoleyAuthor Commented:
OK, let's take this step by step. Previous, I followed the procedures given in another EE posting: http://www.mssqltips.com/tip.asp?tip=1523

I have created an operator: 'Mark Foley'

I set up the SQL Server Agent, Alert System. Mail System: Database Mail. I called my mail profile "database maintenance"

I restarted SQL Server Agent

Somewhere in that process I was asked if I wanted to send a test email to the new operator. I can't find that setting now, but I did request a test email and it was delivered OK.

I then added a 'Notify Operator Task' to my maintenance plan.  I specified operator 'Mark Foley' and in Notification message to send I put "HPRS SQL Server backup finished."

After the maintenance plan ran, I got the error shown below.

So, what am I missing? Let's forget about attaching files for the moment and help me figure out what is wrong so far.

Thanks.

Failed:(-1073548784) Executing the query "EXECUTE msdb.dbo.sp_notify_operator @name=N'Mark Foley',@body=N'HPRS SQL Server backup finished'
" failed with the following error: "Cannot insert the value NULL into column 'permission path', table '@temp'; column does not allow nulls. INSERT fails.
No global profile is configured. Specify a profile name in the @profile_name parameter.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
0
All Courses

From novice to tech pro — start learning today.