Post reply

Alert not firing ... what am I doing wrong ?

  • March 26, 2004 at 2:13 pm

    #66353

    Hello -

    I'm trying to figure out how to set up alerts in SQL Server.  Here is what I did to try and test it.

    set up a test alert to fire on any SQL syntax error on any database (015 - Syntax Error in SQL Statement).

    response is a net send to a valid operator. 

    So then I go into QA and write a bad query.  Sure enough this gets a level 15 error message which matches

    the level of the Syntax Error alert event.

    But no matter how many times I run the query, the alert is never fired and the net send is never performed.

    Why?  (I don't care so much about the net send, I could figure that out if the alert was firing, but it is not. What did I miss? [SQL Server Agent is running, I stopped/restarted the SQL Server, Agent is auto-start when SQL Server starts] )

    TiA 

  • March 26, 2004 at 2:24 pm

    #500757

    Try specifying the alert for error 170 instead of severity.

  • March 28, 2004 at 6:02 pm

    #500819

    The trouble is that sev 15 errors are not event logged by default.  So setting up the alert for sev 15 doesn't achieve anything because the trigger for alerts is the event log.

    If you do as racosta suggests and set it for error 170 then EM prompts you, saying that particular error is not logged.  At that stage you can respond saying yes I'd like it to be logged from now on.

    So, it will work for error 170.  But if you want all 163 sev 15 messages to fire your alert then you'd need to update them all within sysmessages.

    PS. Wanna know a good way to fill up your event log real fast?  ....Log all sev 15 messages and then write an alert handler with a syntax error in it.  D'oh!

     


    Cheers,
    - Mark

  • March 29, 2004 at 9:20 am

    #500900

    thanks - Like I say I'm just trying to come up the learning curve, so I don't intend on logging all sev15 errors, just want something I know will fire to see what I'm doing.

  • March 30, 2004 at 1:54 am

    #501019

    sqlagent needs to be restarted when you activate the first alert.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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