March 16, 2011 at 11:42 am
In my maintenance plain I have a notification set-up to tell the operator the job is ended. The email which I receive from the job has in the Subject line "SQL Server Message". Is there anyway or where can I change the information that appears in the subject line of the email ? In the "Notify Operator Task" at the bottom where the "notification message to send" is, I though that was the place.
March 16, 2011 at 11:48 am
If you want more control over the e-mail, set up DBMail, and use sp_send_dbmail. You can control the whole message that way. Pretty easy to set up and use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 12:17 pm
Why do you have notification set up in the plan? Why not set it up on the job as a "Notify Operator when job is completed?"
You need an operator, but I don't think you need db_mail enabled for it. And it usually titles the email "SQL Server Job System: "My Job Name" completed on .... ".
Of course, we have DB Mail enabled and use Operators, so I'm not sure about my statement that the two can be mutually exclusive.
March 16, 2011 at 12:30 pm
I have the job set up as to "Notify Operator when job is completed" and the end, that's the last task and I'm wondering should is that the way it should be.
March 16, 2011 at 12:35 pm
I'm not sure what you mean by "and the end". Could you clarify?
Notifications on jobs are not related to job steps and not configured by step. They are a separate component of the job setup, configured on the job level.
March 16, 2011 at 12:50 pm
Brandie Tarvin (3/16/2011)
I'm not sure what you mean by "and the end". Could you clarify?Notifications on jobs are not related to job steps and not configured by step. They are a separate component of the job setup, configured on the job level.
There's a "Notify Operator" step that can be added to maintenance plans. That's what's being discussed here. It has a "Subject Line" space, but isn't doing what he wants with it.
It's not at the job level, it's in the plan, and you can notify at various steps or under various flow-control conditions.
I just use a SQL step that calls sp_send_dbmail instead of that, because it's more flexible and controllable. They both use DBMail, but calling the proc directly gives me control over all of its parameters, instead of just the ones in the maintenance plan wizard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2011 at 4:55 am
GSquared (3/16/2011)
Brandie Tarvin (3/16/2011)
I'm not sure what you mean by "and the end". Could you clarify?Notifications on jobs are not related to job steps and not configured by step. They are a separate component of the job setup, configured on the job level.
There's a "Notify Operator" step that can be added to maintenance plans. That's what's being discussed here. It has a "Subject Line" space, but isn't doing what he wants with it.
It's not at the job level, it's in the plan, and you can notify at various steps or under various flow-control conditions.
Thanks for the clarification, Gus.
I think job notifications (rather than plan notifications) could help him out because it would contain a less generic subject, without the config hassle of DB Mail. But DB Mail would definitely let him configure every aspect of the email, if that's what he wants to do.
March 17, 2011 at 7:58 am
Ok, I'm using sp_send_dbmail but the if my maintenance plain is creating a text document with the date and a runtime as the name of the file how do one have include this file as an attachment (@file_attachments = path\filename) when the filename changes each time the plan is run?
March 17, 2011 at 8:20 am
kd11 (3/17/2011)
Ok, I'm using sp_send_dbmail but the if my maintenance plain is creating a text document with the date and a runtime as the name of the file how do one have include this file as an attachment (@file_attachments = path\filename) when the filename changes each time the plan is run?
Are you creating your Maint. Plan through the GUI or through BIDS?
March 17, 2011 at 8:46 am
The Plain has been created via GUI (Management Studio)
March 18, 2011 at 5:44 am
kd11 (3/17/2011)
The Plain has been created via GUI (Management Studio)
FYI: It's "plan", not plain. There's no "i" in the word.
The GUI won't help you with this. None of the available maintenance tasks will allow you to do what you need.
Open up your plan dtsx file in BIDS and edit it there. The maintenance tasks are still available, but in BIDS you can add other tasks (non-mp tasks) that will assist you.
You can use an Execute SQL Task to pull the name via xp_cmdshell, or use a For Each Loop Container. Then store the file name in a variable and use the Send Mail Task to attached the file for emailing. Then it won't matter what the new file name is. SSIS is pretty dynamic.
One thing to remember. The maintenance plans are actually SSIS packages, but they only have a limited number of tasks and configurability options. What you're looking for is more advanced than the GUI can support. Hence the reason you need to learn how to work more advanced SSIS packages and explore what BIDS can do for you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply