Post reply

Maintenance Plans and Database Mail

  • July 26, 2010 at 6:29 am

    #144373

    I'm doing relatively simple things with my maintenance plans on SQL Server 2005 Standard: mainly backup and cleanup with a few specialized but simple scripts thrown in. I've been using Database Mail as a confirmation that the jobs run. I'd like to be able to separate the multitude of e-mails so that I know from the Subject Line what's going on immediately. Right now all the messages have the subject "SQL Server Message" but I'd like them named with the plan or subplan name. Anyone have an idea on how to do this with T-SQL.

    I've tried clicking on the view T-SQL button in the plan but I get nothing returned.

    Thanks in advance,

    Terry Grignon

  • July 26, 2010 at 7:26 am

    #1198183

    You cannot edit the Mail Content of the one sent by the Native Maintenance Task.

    You can create your own SSIS Package and include Mail Task, in which you can modify how the e-mail is sent.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • July 26, 2010 at 12:34 pm

    #1198379

    Hi Terry,

    You can configure an email notification within the Maintenance Plan(MP) itself.

    Just drag & drop a TSQL Task on to the MP and enter the following TSQL script for the database mail

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name ='Database-mailProfile',

    @recipients=N'name@domain.com',

    @body='Message Body',

    @subject ='Message Subject';

    And now by using precedence constarints you can drag and drop the precedence constraint ( Success or Failure ) from the task on to this DBMail TSQL task so that the DBMail TSQL task is fired as soon as the task that calls this TSQL DBMail task succeeds or Fails. So in this way you can get a status email based on each task in the MP.

    Also you can configure One DBMail TSQL task for the entire Subplan so that you can get an email based on the subplan execution status.

    So with this method you can get the email with meaning full subject and body whenever the MP Job runs.

    Thank You,

    Best Regards,

    SQLBuddy.

  • July 26, 2010 at 1:57 pm

    #1198430

    Thanks a lot! That looks like something to try.

    Terry

  • December 18, 2014 at 12:37 pm

    #1765561

    Thanks for that post. It worked like a champ. Perfect.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply