Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting up alerts for specific error messages Expand / Collapse
Author
Message
Posted Friday, May 08, 2009 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 13, 2009 7:29 AM
Points: 9, Visits: 35
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..
Post #713232
Posted Friday, May 08, 2009 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 13, 2009 7:29 AM
Points: 9, Visits: 35
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??
Post #713324
Posted Thursday, July 02, 2009 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 6:39 AM
Points: 1, Visits: 40
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.
Post #746419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse