How to log SQL Server error messages to SQL/Windows error logs?

Is there an option how to log various SQL Server error messages to SQL Server Error Log and to Windows Error Log? There is one and very simple: we can use system stored procedure sp_altermessage to alter system (message_id < 50 000) messages or user defined messages to be forwarder to both logs.Let see an example:

EXEC sp_altermessage 102, 'WITH_LOG', 'true'
EXEC sp_altermessage 208, 'WITH_LOG', 'true'

SELECT * F_ROM dbo.table1
SELECT * FROM dbo.invalid_name

We have altered two system messages to be stored in SQL/WINDOWS logs:

SQL Server Log:

WINDOWS Applications Log:

Both messages are logged there as expected.

The effect of sp_altermessage with the WITH_LOG option is basically the same as of the RAISERROR WITH LOG parameter, except that sp_altermessage changes the logging behavior for all messages fired inside the engine.

Leave a Reply

Your email address will not be published. Required fields are marked *