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' GO SELECT * F_ROM dbo.table1 GO SELECT * FROM dbo.invalid_name GO
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.