How to add a user-defined SQL Server error message and send message to the Windows event log

I discussed in an earlier post on triggering an error message to write to the Windows event viewer. This can be very useful - as log readers can  use one source to gather error messages and trigger alerts.

If you're managing large amounts of SQL Server instances - standardising is essential. One of the standardisations is to add user-defined error messages dedicated to the DBA team. These user-defined messages can be used to give information for alerts - for example - failed sql server agent jobs , failed ETL jobs etc

An example of how you could manage this process - is to add 4 (or as many as required) ids.

As part of the standard SQL Server Installation package for SQL Server 2016  this code is executed. The code adds 4 entries into the sys.messages view.
When the dedicated  monitoring system  scans the Windows event viewer , it includes a check for these error messages i.e event ids: 55000 , 60000, 70000 and 75000.

EXEC sp_addmessage 55000, 16, 'DBAERROR: %s', 'english', false,replace
EXEC sp_addmessage 60000, 16, 'DBAWARNING: %s', 'english', false,replace
EXEC sp_addmessage 75000, 16, 'DBAERROR: %s', 'english', false,replace
EXEC sp_addmessage 70000, 16, 'DBAWARNING: %s', 'english', false,replace

To  exploit these messages as part of code this is an example. This example will add the message 'DBAERROR:A message from MSSQL$TEST' to the Windows event application viewer.

EXEC xp_logevent 60000, 'A message from MSSQL$TEST', error

Read more on  How to write to a sql error to windows server event viewer