How to alert on system errors
Alerts in SQL server rely on the error being logged to
the event log. Not all system messages are logged which means you can't alert on
them. In SQL 2000 you could hack the sysmessages table, however in SQL2005 you
can't.
To get round this in SQL 2005 you can use the try catch block. Catch the
error and reraise it. You then build an alert on the default user error number
50000. This is far from ideal but it does work, sometimes.
It doesn't work in those cases where the error is not caught by the CATCH
block, hte primary reason is errors that close the connection i.e. a backup
failure. For these you need a client to catch the error i.e. SQL Agent.
If you don't want to add an alert for error 50000 then you can add a specific
piece of text and configure the alert for that piece of text.
begin
try
--Some TSQL that may error
RAISERROR('This is a test error
message',15,1)
end
try
begin
catch
declare @message
nvarchar(2048)
set @message
= 'Error during this test code : '
+ error_message()
RAISERROR(@message,15,1) WITH log
end
catch
-