Alerts for Error Numbers do not work

Question

Alerts for Error Numbers do not work

DBADave

SSChampion

Points: 10962
More actions
August 2, 2007 at 11:44 am

#118027
I'm unable to get an alert to fire for Error 2627, Primary Key Violation. I have followed BOL's instructions for creating an alert, which appears to be nearly identical to 2000, however the alert will not work. I can see the error number in a Profiler trace along with the Severity number. I've also tried creating alerts for other error numbers and had the same result. The BOL instructions are as follows:
To create an alert using an error number
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent
3. Right-click Alerts and then click New Alert
4. In the Name box, enter a name for this alert.
5. Check the Enable check box to enable the alert to run. By default, Enable is checked
6. In the Type box, click SQL Server event alert.
7. In the Database name list, select a database to restrict the alert to a specific database
8. Click Error number, and then type a valid error number for the alert
9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert when message contains, and enter a keyword or character string for the Message text. The maximum number of characters is 100
What am I doing wrong?
Thanks, Dave

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

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

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

I'm not sure how to do this through the GUI, but here is a copy of the script I used previously to add an alert for constraint violations:

EXECUTE msdb.dbo.sp_add_alert @name = N'Constraint Violation', @message_id = 547, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Constraint Violation', @operator_name = N'DBA', @notification_method = 1

noeld SSC Guru Points: 96590 More actions · Answer 2

you need to enable that event as a logged event :

select * from master.sys.messages where message_id = 2627 and language_id = 1033

is_event_logged = 0.

To change it use sp_altermessage

Cheers,

* Noel

DBADave SSChampion Points: 10962 More actions · Answer 3

Hi Noel,

That's what I originally thought, but I found sp_altermessage only applies to user messages, which is anything > 50,000.

Thanks, Dave

noeld SSC Guru Points: 96590 More actions · Answer 4

Well I have to admit you are "right".

M$ removed the ability to alter system messages to "logged" . It has been posted in the connect site for a long time and there are a lot of people affected with this problem

* Noel

DBADave SSChampion Points: 10962 More actions · Answer 5

I've lost count of the problems we've encountered with SQL 2005. I've opened around 9 or 10 tickets with Microsoft support regarding various issues and most are either the result of functionality that was removed and not documented or the documentation is burried within some other document, incorrect documentation that they said they would correct, no documentation on a particular issue or simply a bug. We're now starting to contemplate holding off future migrations of 2005 and wait until 2008 is released, which I'm guessing will be some time in 2009.

Thanks and sorry for venting.

Dave