Throw and RaiseError of level 16 do not cause alert to occur

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 1

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