1

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: enter image description here


What am I missing?

  • What are you trying to do exactly? sys.messages isn't a log of all the errors that have been raised. You add to it using sp_addmessage – Mark Sinkinson Sep 9 '14 at 9:22
  • @Mark Sinkinson, I probably looked in the wrong place but the Occur Counter in History tab of the alert is also equal to 0 – Yurii Hohan Sep 9 '14 at 9:29
5

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
  • I completely missed the point of sys.messages, but throwing a known error like RAISERROR (45037, 16, 1) does not help too – Yurii Hohan Sep 9 '14 at 10:00
  • @YuriiHohan Added a bit more detail on severity 16 errors. You need WITH LOG – Mark Sinkinson Sep 9 '14 at 10:08
  • Thank you for clarifications, you are right - it worked out. The only thing I do not understand why sql like select * from UnknownTable.UnknownColumn does not raise an alert. Am I missing smth else? – Yurii Hohan Sep 9 '14 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.