views:

757

answers:

1

In SQL Server 2005 I have a maintenance plan set up to back up databases. I have a notify task. I am getting a blank email. The maintenance job history shows the error:

Could not generate mail report. An exception occurred while executing a Transact-SQL statement or batch.Supply either @id or @name.

I have set up database mail, I have enabled alerts in SQL Server Agent. I have set a default profile. I have restarted the agent.

Test emails from database email do work properly.

What do I do to troubleshoot and resolve this error?

+1  A: 

Th first thing to do is bring up the Management Studio, expand the management node, right click on Database Mail and select Send Test E-Mail

Until this functionality is working properly, there is no point in trying anything else.

If you have not setup Database Mail, right click on the node and select Configure Database Mail

EDIT, (included comments into answer):


If that is working, It could be that the send alert section of your maintenance plan was not setup correctly. While it is possible to edit the failing plan, it may be easier to use the wizard to recreate the whole thing, paying particular attention to the notifications section - especially when other maintenance plans are working OK.

Others report (thanks Brent) that they have seen errors like this when maintenance plans are copied from server to server incorrectly, or when a SQL Server is upgraded.

Noah
Noah... thanks. I did do that and it sends the test email without any problems or errors. I also have other maintenance plans that work fine and do not generate this error either. As I said, I am getting the email from this plan, it just also generates this error. I don't understand that at all.
PilotBob
Have you tried to delete and recreate the maintenance plan?
Noah
No, I guess I can try that.
PilotBob
Yes, my best guess is that the send alert section of your maintenance plan was not setup correctly. While you could edit your current plan, it may be easier to recreate the whole plan, paying particular attention to the notifications section.
Noah
I'd second Noah - there's likely a problem with the maintenance plan, and I'd try deleting it and recreating it - especially if other maintenance plans are working OK. I've seen errors like this when maint plans are copied from server to server incorrectly, or when a SQL Server is upgraded.
Brent Ozar
Deleting and recreating resolved the issue. Very strange. It may be because I originally created the MP before I had database mail correctly set up. Thanks Noah and Brent.
PilotBob