March 4, 2010 at 8:52 am
In SQL Server 2005 SP3 running on Windows Server 2003, I have created two Maintenance Plans using the Maintenance Plan Wizard. The Wizard created two jobs. When I execute the Jobs, the text displayed in the Subject Box of the email (returned from the Jobs) states 'SQL Server Message'. I am trying to find a way to change this message to add the server name. How may I change the message that is displayed in the Subject Box of the email sent from the Jobs? The email functionality is configured in the 'Reporting and Logging' Menu Button (Edit the Maintenance Plan to configure.) Then select 'Send report to an email recipient'. There is no options/box displayed to change this message. I may have to change the Database Mail Account Name to include (reflect) the server name.
Any advice is appreciated, Kevin
March 10, 2010 at 9:22 am
Just wondering if anyone has any advice on this issue.
March 10, 2010 at 9:44 am
As far as I now you can't change the subject on those mails. You will have to include another step(s) in the job that does send the e-mail depending on how the previous step terminated, using Database Mail.
Changing the DBMail account as you suggest might be another way, so you know which server it is coming from (ie, not using a generic account across servers).
Using the first option you have more flexibility as you can put whatever info is needed in both Subject and body of mail.
March 11, 2010 at 8:49 am
I change the Display Name in the profile to the instance name. It's not ideal, but it helps.
March 11, 2010 at 3:39 pm
I may be misinterpreting your issue, but instead of using a "Notify Operator Task" use an "Execute TSQL Task" in your maintenance plan. The TSQL code to run would be something like this:
EXECUTE msdb.dbo.sp_notify_operator
@name=N'DefinedOperatorNameGoesHere',
@body=N'This task succeeded. It does a full backup of the system dbs',
@subject = N'SQL SUCCESS - [PutSqlServerNameHere] in Maintenance Plan [Put Maint Plan Name Here]'
In one of my maintenance plans for example, I have the "Backup Database Task" and 2 "Execute TSQL Tasks"-- one of the Execute TSQL tasks is for a "SUCCESS" email as shown-- the other is for a "FAILURE" email which is similar.
If you use a formulaic subject line as shown it's easy to create email rules in your inbox so you can send all "SUCCESS" emails to a system folder and leave all "FAILURE" (or "ALERT", another category I use) emails in your inbox so you can immediately deal with them.
Use a formulaic approach to your subject lines so you can use a few simple email rules to forward "SUCCESS" emails out of your inbox, leaving ALERT/FAILURE emails there for your immediate attention. My standard subject line is "SQL [SUCCESS/ALERT/FAILURE] - [SERVERNAME] in [ROUTINE NAME]"
--EDIT--
OH... Reporting and logging.... how about that; I've never used that. Instead, open the toolbox and drag the "Execute TSQL" task over and set up as described above. See if that does what you want.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply