SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting up alerts for specific error messages


Setting up alerts for specific error messages

Author
Message
Ranch
Ranch
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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..
Ranch
Ranch
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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??
Bassman-970529
Bassman-970529
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search