Question :
The next alert has been created:
USE [msdb]
GO
/****** Object: Alert [Notify] Script Date: 09/09/2014 11:38:31 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Notify',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
I expect that the next code will cause it to occur:
THROW 51000, 'The record does not exist.', 1;
Or the next code:
RAISERROR ('Error raised.', 16, 1);
System Messages does not contain the error messages:
SELECT *
FROM sys.messages
WHERE language_id = 1033
and severity>=16
ORDER BY severity asc, message_id asc
I also have no events in History tab:
What am I missing?
Answer :
From reading the BOL article on sp_add_alert
[ @severity = ] severity – The severity level (from 1 through 25) that defines the alert. Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to define the alert, severity must be 0.
If you want to add alerts for user-defined error messages, you need to add your error message to the sys.messages
table using sp_addmessage
EXEC sp_addmessage @msgnum= 90210, @severity=16, @msgtext='Error raised.', @lang= 1033;
To actually raise the alert for the error, there are a few caveats you must be aware of (seriously…read the online guide…that’s all I’m doing here…)
These are the circumstances under which errors/messages generated by
SQL Server and SQL Server applications are sent to the Windows
application log and can therefore raise alerts:
• Severity 19 or
higher sys.messages errors• Any RAISERROR statement invoked with WITH LOG syntax
• Any sys.messages error modified or created using sp_altermessage
• Any event logged using xp_logevent
So a RAISERROR
for a severity 16 error will not just alert you by default, you would need to add WITH LOG
after it.
RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG