Post reply

Alerts for Error Numbers do not work

  • 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

  • August 2, 2007 at 12:47 pm

    #724302

    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

     

  • August 2, 2007 at 1:22 pm

    #724311

    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

  • August 2, 2007 at 1:30 pm

    #724323

    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

  • August 3, 2007 at 9:01 am

    #724543

    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

  • August 6, 2007 at 7:47 am

    #724823

    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

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

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