patternsqlMinor
SQL Server Agent Alerts using severity 16
Viewed 0 times
sqlagentusingserverseverityalerts
Problem
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:
I've verified that it's a severity 16 by checking message table.
But this does not raise an alert. Is it possible to raise an alert on object not been found?
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 SomeObjectThatDoesNotExistI'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 = 2812But this does not raise an alert. Is it possible to raise an alert on object not been found?
Solution
Finally found the answer to my question.
You need to enable the
Example code:
Here is the blog post I found to answer my question
Reference
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
Code Snippets
EXEC sp_altermessage @message_id = 2812, @parameter = 'WITH_LOG', @parameter_value = 'true'Context
StackExchange Database Administrators Q#208361, answer score: 2
Revisions (0)
No revisions yet.