Setting up alerts for specific error messages

• Ranch

  Mr or Mrs. 500

  Points: 511

  More actions

  May 8, 2009 at 11:57 am

  #384543

  Hi,

  I am creating a job step where it executes a stored procedure.

  Inside the stored procedure , based on a condition I perform RaiseError('Not Valid', 16,1)

  So when ever this error occurs i need to notify via email to me.

  So for this scenario, I have set alert in Job.

  I have choosen the following ,

  I created an alert with a particular name, enabled it, Type as SQL Server Event Alert,then my Database Name,I choose Alerts will be raised based on Severity - 016 then checked the box 'Raise alert when message contains' and then Messagetext I wrote 'Not Valid'.

  And then also in the response tab I have notify operators I choose my Name. And then tab Options I choose - Include Alert error text in Email.

  When executed that particular job step, it executed that stored procedure and raised that error.However i didnt get any alerts at all...I went and i checked History tab in alerts afetr this and there is no indication that such alert occured.

  What I am i missing? Any help??

  PS : There is no problem in Database Mail and other configurations for email because I get notification for the others job..

• Ranch

  Mr or Mrs. 500

  Points: 511

  More actions

  May 8, 2009 at 1:55 pm

  #991442

  Yes,

  I managed to fix this , but got into another problem.

  1. I added a message in sys.messages by executing a stored procedure

  EXEC sp_addmessage @msgnum = 50603,

  @severity = 16,

  @msgtext = 'Invalid number'

  2. Then used this messageid to raise error in the stored procedure .

  like RAISE ERROR(50603,16,1) WITH lOG - i initially had only RAISE ERROR (50603,16,1) it didnt work. I changed that to RAISEERROR(50603, 16, 1) WITH LOG and it worked. All severity levels less than 19 should have WITH LOG or use xp_logevent.

  After these two changes the alerts worked. However i was getting continously alerts for long time more than 20 alerts until I volunterly changed the stored procedure back to RAISE ERROR(50603,16,1) which will not alert.

  I am wondering why I am getting continous alerts though I was not executing the job. I just executed the store procedure from query analyser to raise the error..

  Any help on this??

• Bassman-970529

  Valued Member

  Points: 67

  More actions

  July 2, 2009 at 10:04 am

  #1019281

  Have you checked in the repsonse tab that it isn't running the job as a response to the alert being raised ?

  I had this problem where through Management Studio I could not remove the tick from the box on "run job" in the response tab, so was getting spammed by alert emails as I ran the job, the alert was raised, which then ran the job agaiin, which alerted etc etc.

  Whatever I did, restarting the agent etc I could not get my removal of the tick box to stick.

  So finally I scripted the alert to a new window, deleted the original, and modiied the script to remove all job refs before running it to recreate the alert. Bingo ! The alert was created with the job part geyed out and box not ticked. Am now very happy that it will do what I want.