unable to modify "subject line" of the sql server 2005 email notification

Question

unable to modify "subject line" of the sql server 2005 email notification

NewToSQLserver

Right there with Babe

Points: 763
More actions
July 1, 2009 at 2:49 pm

#131761

I'm giving a task to setup the email notification on sqlserver 2005 whenenver the database backup finishes to sent to the dba team. I was able to make it work except the subject line. It keeps saying "SQL Server Message". I googled it but no luck and saw a few people had the same problem like mine but nobody responded back to them. I saw there is a sqlserver agent properties showing the subject line field but it is for the "pager emails". I did put in the info in the subject line for the "pager emails" section adn clicked OK. However, when I went back to the properties of the sql server agent, it did not the information I put it in.
This is the last piece of information that I need to modify before I can implement on all production databases. I checked around a couple DBAs that I know but they don't know either. Therefore, I post it on here to see if somebody out there can help me out.
Thanks,
Trina

Viewing 10 posts - 1 through 9 (of 9 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I don't believe you can change it..

With that said, you can still use an inbox rule to handle distributions, you just have to look at the body more closely..

Can you explain exactly what you hope to accomplish..

CEWII

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 2

I want to receive an email notification with a subject line showing server name and either system databases backup or users databases backup. I have no problem of receiving the email notification everytime it completes the backup. What is so irritated that the subject line of the email showing "SQL Server Message". I have to open the email to read the content to know what databases belong to what server was backup.

Trina

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I think you could accomplish this using a rule in Outlook, it isn't NEARLY as easy as what you are trying to do this way, but I see your goal..

CEWII

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 4

My site does not use Outlook. We use Groupwise. However, I want to thank you for providing me a suggestion so I can know where to do the reseach.

Trina

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

Instead of the easy way, using Notifications, you might want to use a call to an SP passing as parameters for example the server name (although that can be pulled within the SP) and the backup type and in the Sp just use a call to Database Mail where you can set the Subject and many other things.

The call to the Sp would be another step in the Backup job and activated by an On Success of the backup step.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

tracy.fry SSC Journeyman Points: 93 More actions · Answer 6

hello, i don't know if you found a solution to the problem you had - i overcame this issue by adding an 'Execute T-SQL Statement Task' to the maintenence plan. The T_SQL statement uses the following type of syntax that you could amend to suit your needs -

EXECUTE msdb.dbo.sp_notify_operator @profile_name=N'IS_DBA', @name=N'IS_DBA', @subject=N'SVR-xxxxxx daily backup' .......being the text you would like to appear in the subject line , @body=N'SUCCESS SVR-xxxxxx ..............being whtever you want to appear in the body of the e-mail - to test the syntax i found it easier to run the script through the query analyser so that i could parse the script and execute the query without having to run the entire job - i.e. the daily backup job.

I hope this helps

Tracy

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 7

Hi Tracy,

I did not able to get it to work. However, I got another task to do the setup the serverity level alert that will send an email notification to the DBA team but it has not been successfully. I posted out a few weeks ago but still have not have a response. I'm having a heachache right now with this setup.

I will put this "unable to modify "subjectLIne" on hold until I troubleshoot the serverility level alert setup. I definitely keep your information.

Thanks,

NewToSQLServer

Anne Thom SSC Journeyman Points: 82 More actions · Answer 8

Tracy thank you so much for solving my problem. Why didn't I think of that? You are a wonderful person!

mstjean Hall of Fame Points: 3310 More actions · Answer 9

This repeats tracy's post a bit, but what the heck.

Both of these require Database Mail. You send the email to a defined SQL Operator; An operator CAN be a domain group. Use @@servername.

If you're using a Maintenance Plan-- add 2 "Execute tsql statement" tasks, similar to this:

--SUCCESS email code in "Execute tsql statement"

declare

@namenvarchar(128),

@subjectnvarchar(256),

@bodynvarchar(max)

set @subject= 'SQL SUCCESS - [' + @@SERVERNAME + '] BACKUP ALL USER TLOGS'

set @body= 'This process completed successfully'

set @name= 'AnOperatorName'-- this must be a defined operator on the sql server

EXECUTE msdb.dbo.sp_notify_operator '',null,@name,@subject,@body

If you're using a SQL JOB, add 2 steps and use step logic to send either SUCCESS or FAILURE, similar to this:

-- FAILURE email code in a SQL JOB step

EXECUTE msdb.dbo.sp_notify_operator

@name=N'AnOperatorName',

@body=N'Email body',

@subject = N'SQL FAILURE - [Servername here] in SQL JOB [sql job name here]'

ALSO....

I don't know about you but I don't like inbox clutter. So I have a rule set up in my Outlook email that automatically forwards emails with subject "SQL SUCCESS" to a SystemMail folder. I leave "SQL FAILURE" and "SQL ALERT" (for other "uh-oh" conditions) in my inbox.

Cursors are useful if you don't know SQL