SQL Server Agent Alerts using severity 16

Question

My work is currently deleting lot of stored procedures, views, tables and so on.

My worry is that if we miss judge a object and it goes into production that we'll only catch the errors when an user reports it.

I've created an alert using type "SQL Server event alert" and severity 16 and checked the e-mail for operator.

So, time to test the task:

EXEC SomeObjectThatDoesNotExist

I've verified that it's a severity 16 by checking message table.

USE MASTER

SELECT *
FROM SYS.MESSAGES m
WHERE m.language_id = 1033 AND
        m.message_id = 2812

But this does not raise an alert. Is it possible to raise an alert on object not been found?

If I recall correctly, they'll only trigger for logged events, which that one doesn't appear to be. If you run RAISERROR('Hello', 16, 1) WITH LOG; does it produce an alert? — Erik Reasonable Rates Darling, Commented May 31, 2018 at 12:20
Yes, it does. Thanks. I did see that on another post on here. I'd really want it to work with other objects like EXEC spName and so on. If that is even possible. — Paul, Commented May 31, 2018 at 12:25

Paul · Accepted Answer · 2018-05-31 14:52:39Z

2

Finally found the answer to my question.

You need to enable the is_event_logged in the sys.messages table.

Example code:

EXEC sp_altermessage @message_id = 2812, @parameter = 'WITH_LOG', @parameter_value = 'true'

Here is the blog post I found to answer my question Reference

answered May 31, 2018 at 14:52

Paul

1514 bronze badges

Add a comment |

1 Answer 1