May 8, 2009 at 11:57 am
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..
May 8, 2009 at 1:55 pm
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??
July 2, 2009 at 10:04 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply