June 12, 2008 at 12:49 pm
Hi,
if i setup alert in SQL Server Agent for lets say severity 24,when i query the sys.messages for severity = 24, i see that there are 9 rows with severity = 24.
is it mean that this alert is listening to all those 9 error ? or only 1 of them?
the same with severity 23 if i listen to that severity would i capture alert 9100(Possible index corruption detected. Run DBCC CHECKDB.) on that severity 23 or should i make an alert to that 9100 error special?
THX
June 12, 2008 at 2:35 pm
avipenina (6/12/2008)
Hi,if i setup alert in SQL Server Agent for lets say severity 24,when i query the sys.messages for severity = 24, i see that there are 9 rows with severity = 24.
is it mean that this alert is listening to all those 9 error ? or only 1 of them?
the same with severity 23 if i listen to that severity would i capture alert 9100(Possible index corruption detected. Run DBCC CHECKDB.) on that severity 23 or should i make an alert to that 9100 error special?
THX
On SQL 2005 "iis_event_logged" is turned off for many messages and M$ killed our ability to choose what's get logged and "therefor alerted on" 🙁
* Noel
June 12, 2008 at 2:51 pm
i didn't understand anything from your answer.
June 12, 2008 at 4:16 pm
avipenina (6/12/2008)
i didn't understand anything from your answer.
OK sorry about that. Let's go step by step.
Alerts are fired if the message in sysmessages is "logged"
The Alert Engine checks for "logged" messages only and therefore little activity is done by the Engine by default.
In 2000 you could change *any* of the messages to be "logged" and then you cud use the Alert Engine to act upon it
In 2005 M$ removed such ability and now only user_defined_messages can be turned on and off at will.
In 2005 when you query sys.messages there is an "is_event_logged" column that tells you if you can or not use that message in the Alert Engine.
Is that better ?
* Noel
June 12, 2008 at 11:30 pm
yes it's better.but for my second question - if i setup alert to listen for severity 23 and message id 9100 is in the "logged message" in the sys.messages,if error 9100 occur i will get a notification or not?
if so how can i raiserror 9100 just for testing purpose?
THX
June 13, 2008 at 6:01 am
hi,
You can define alerts for a severity or for an error number.
If you define an alert for severity 23, all errors with that severity will trigger the alert. If you just define the error number, the alert will fire if that error occures, regarding the severity.
You can test an error with the RAISERROR command. See BOL
Wilfred
The best things in life are the simple things
June 13, 2008 at 9:40 am
i try to look in BOL for RAISERROR 9100,and i can't make it.
can you help me Plz with the syntax to raise the ERROR 9100.
THX
June 13, 2008 at 11:14 am
avipenina (6/13/2008)
i try to look in BOL for RAISERROR 9100,and i can't make it.can you help me Plz with the syntax to raise the ERROR 9100.
THX
The ability to raise "ANY" error was removed from SQL2005 you could try with a different error code though like (FROM BOL):
EXEC sp_addmessage @msgnum = 50005,
@severity = 10,
@msgtext = N' ';
GO
RAISERROR (50005, -- Message id.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
EXEC sp_dropmessage @msgnum = 50005;
GO
* Noel
June 13, 2008 at 12:46 pm
this i know.
I'm talking about the ability to raise an error message that is in the sys.messages.
you say that i can't raise any error that is shipped with the product(for example error 9100)?
if i setup alert with error number for example 21414 that in the sys.messages it's not logged(0),still the error will raise or not?
can i change the not logged error (0) to logged(1)?
THX
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply