Post reply

Force RaiseError for selected sysmessages

  • May 24, 2012 at 10:14 am

    #392339

    Not a very succint title, wan't sure how to put it ... basically, our monitoring team are putting together a module to scrape the event log for SQL errors

    I have a list of the error numbers I want to look for from sysmessages and have confirmed that they would get written to the event log.

    However, they need an example of each message I want to scrape for, to be raised as an error in the event log in order that they can configure whatever it is that they're making!

    Is there a way I can force each of the selected events in sysmessages to write itself out to the error log? specifics like database name are not necessary. I think I need to look at RaiseError, but not too sure where to start 😛

  • May 24, 2012 at 11:11 am

    #1492696

    IIRC, the decision to log something is from the RaiseError command, not the error. If the WITH LOG parameter is used, it's logged. If the severity is high enough, this parameter is required.

    http://msdn.microsoft.com/en-us/library/ms178592.aspx

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 24, 2012 at 5:40 pm

    #1492894

    Just to add to what Steve said, you can see which system errors are automatically logged:

    SELECT *

    FROM sys.messages

    WHERE is_event_logged = 1

    AND language_id = 1033

    ORDER BY message_id;

    Nitpick: sysmessages is deprecated, use sys.messages

    If you want to create a new error message that is logged whenever raised you can do so like this:

    EXEC sys.sp_addmessage

    @msgnum = 50001,

    @severity = 16,

    @msgtext = N'Hello!',

    @with_log = 'true';

    RAISERROR(50001,-1,-1)

    And you'll see two sequential entries like this in the error log:

    Error: 50001, Severity: 16, State: 1.

    Hello!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 25, 2012 at 2:36 am

    #1492995

    Thanks both,

    To clarify, it's the non-custom messages I'm trying to get to write to the log, eg - a 9002 log full event without having to force the log to become full. The code snippet above is what I used to get a list of the logged messages from sysmessages.

    I tried a Raiserror on 9002 & 3041 & got the following:

    Msg 2732, Level 16, State 1, Line 1

    Error number 9002 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000

    So I'm guessing that it can only be used on custom messages & a subset of those in sysmessages.

  • May 25, 2012 at 7:41 am

    #1493161

    N.D (5/25/2012)

    Thanks both,

    To clarify, it's the non-custom messages I'm trying to get to write to the log, eg - a 9002 log full event without having to force the log to become full. The code snippet above is what I used to get a list of the logged messages from sysmessages.

    I tried a Raiserror on 9002 & 3041 & got the following:

    Msg 2732, Level 16, State 1, Line 1

    Error number 9002 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000

    So I'm guessing that it can only be used on custom messages & a subset of those in sysmessages.

    We are not allowed to raise those errors as they belong to the database engine.

    Another option is to use Alerts to do something specific for those errors you're interested in watching.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 25, 2012 at 8:22 am

    #1493195

    I would lean towards alerts here, as opc.three suggested.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • June 13, 2014 at 4:14 am

    #1721391

    Sorry to hijack this thread but i have a very similar requirement.

    I have SQL Server agent alerts set up to notify me of errors with a severity of 16 and upwards; but these are only emailed out if the error is also written to the event log.

    So i thought i would modify the "Is_Event_Logged" column within the sys.messages table for all those errors i would like to be notified of; but i get an "Ad hoc updates to system catalogs are not allowed" message when i try to do this.

    I have read that i may be able to get around this using the DAC but i wanted to check if this is a generally acceptable way of achieving what i want or if i should just put up with the fact that i cannot be alerted of all errors as i would like.

    Thanks in advance.

  • June 13, 2014 at 5:54 am

    #1721424

    Check out system stored procedure sys.sp_altermessage.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 13, 2014 at 6:46 am

    #1721443

    Thanks very much, that has worked perfectly.

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

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