One of the reasons I’m not a huge fan of Maintenance Plans is that they do everything in a very set way. My biggest gripe around this remains around lack of smarts in optimisation of indexes but I recently came across another annoyance and that is that the “Notify an operator” task relies on a public or global operator. While you can quite happily send an email with TSQL by specifying a private mail account, if there is no public account this task will fail and you will get a message like below:
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 94
No global profile is configured. Specify a profile name in the @profile_name parameter
The error gives you most of what you need to know to figure this one out, although not clearly enough for my liking. It tells us the problem is in the procedure that sends dbmail, so we know it’s a database mail issue. And it tells us that there is no global profile defined so we should specify a value for @profile_name. Well….yes I would do that….if I was using TSQL. But I’m not, I’m using a maintenance plan. So I have two options.
- Instead of a notify operator task, I can use a TSQL task and write a piece of custom tsql to send myself a message, in which case I can specify my private profile. Or…
- Define a new default profile or change an existing private profile to be the default.
Now, in my mind a tsql task is a fine option because you can do a few additional things which can’t be done with a notify operator task(like attach a file such as the error log for example) , but it can be a pain cutting and pasting the TSQL across all your various notifications. So if you want to resolve the error all you need to do is:
1. Open database mail configuration by logging into SQL Server Management studio and expanding Management and right clicking on DatabaseMail, then choosing configure:
2. Choose Manage Profile Security.
3. Configure your profile to be the default profile:
There’s no need to restart anything, your maintenance plan should now send email fine.