Notify operator task

[MasterRacker]

SQL 2005 SP3

I've enable database mail via SAC. In SSMS I've set up an operator with my e-mail address. I created a test maintenance plan that consists solely of a Notify Operator task just to send a test message. The plan fails with the following log:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035
Report was generated on "XXXXX".
Maintenance Plan: TEstPlan
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Notify Operator Task (XXXXX)
Notify Operator on Local server connection
Operator: SysAdmin
Task start: 2009-07-22T16:17:07.
Task end: 2009-07-22T16:17:08.
Failed:(-1073548784) Executing the query "EXECUTE msdb.dbo.sp_notify_operator @name=N'SysAdmin',@body=N'Test plan executed'
" 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.

What am I missing?



Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

I've made some progress but this still doesn't work. What I've done (from the beginning):

1. Enable Database Mail via Surface Area Configuration
In SSMS Object Explorer:
2. Management > SQL Server Logs > Database mail> Configure...: Run the wizard to create a new account and profile and set the profile as a public profile.
3. Management > SQL Server Logs > Database mail> Send test mail: Successfullt sent a test message from the test profile to my e-mail.
4. SQL Server Agent > Operators: Created an operator with my e-mail address.
5. SQL Server Agent, right-click & select Properties > Alert System: enabled my test profile.
6. Created a maintenance plan with a Notify Operator task set to notify my test operator.

The plan still fails. The SQL error log message I get is [260] Unable to start mail session (reason: No mail profile defined).

There is nothing in the Notify Operator task that allows me to choose a profile, so I have to assume it's supposed to use the one I assigned to the SQL Server Agent.

Once again, what am I missing?




Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

Oops, in steps 2 & 3, substitute "Management > Database Mail" for "Management > SQL Server Logs > Database Mail".

DOH!

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

Got it as I was documenting what I've done. In step 2, the wizard does not automatically set the profile created to be the default profile, even if it is the 1st one.

I win!

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]