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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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 & Architect, EE Solution GuideCommented:
>> 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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.