SQL Mailer Alert Subject

Question

question

Josh Meyer asked Jun 21, '10

SQL Mailer Alert Subject

Hello,

Our company doesn't have a SQL DBA so some of the responsibilities fall to our Microsoft Infrastructure Team, which I happen to be on (lucky me)

We have a server running SQL 2005. There was an alert set up to email specific users on error number 50018. The user is asking me if there's a way to enter a custom subject for the alert email he receives. If nothing else, he'd like to drop off the "SQL Server Alert System" from the subject line. Does anybody know of a way that I can do this?

Thank you

dbmail mail alerts

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Jun 21, 2010 at 06:05 PM

can you explain how the subject might vary? What does the user want to be different?

0 ·

Josh Meyer commented · Jun 22, 2010 at 02:51 PM

The user primarily just wants to remove the 'SQL Server Alert System' prefix from the subject line. That way it will just read something along the lines of 'NEGATIVE LEGAL DEDUCTION FAILURE' instead of 'SQL Server Alert System - Negative Legal....' I appreciate your responses on this.

0 ·

Answer 1 · 2010-06-21T16:46:50Z

Fatherjack answered Jun 21, '10

I dont think there is much you can change on alerts themselves. Have you found where they are configured in SSMS? In object explorer under the SQL Agent section, expand Alerts and select the alert you want to work on.

What you could do to get the customisation you want is to use the alert to fire a job that sends the email formed in the way that you want it. I am thinking that if your arent SQL DBAs then that may just be making a load of work that you dont need.

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark commented · Jun 21, 2010 at 05:24 PM

FatherJack, I wonder if a data driven report using SSRS would work.

0 ·

Fatherjack ♦♦ commented · Jun 21, 2010 at 06:05 PM

@Mark, at first thought that is more abstract than making the alert fire a job as I dont know how to get SSRS to fire on a specified error number. I guess it could run on a schedule and aggregate events in the last x hours ... I get the idea from the question that the event needs more rapid escalation though.

0 ·

Mark commented · Jun 21, 2010 at 06:12 PM

I'm not sure how to get SSRS to fire on a specific error number either unless maybe it checked the log somehow. Thanks for the reply though.

0 ·

Josh Meyer commented · Jun 22, 2010 at 03:09 PM

I've checked out the object in SSMS but I don't see anything in the alert that allows me to specify an email subject. I assume it builds a default subject of 'SQL Server Alert System - ALERTNAME' and I was hoping that I could find this and make a change to it. No such luck so far. Setting up the alert to launch a job that sends out an email is an interesting idea. But I'm not sure how convoluted I want to make this process. I think the user is just going to have to live with the subject that SQL gives them.

0 ·

question