sp_altermessage System Stored Procedure

Alters the state of a sysmessages error.

Syntax

sp_altermessage message_id, WITH_LOG, {true | false}

where

message_id: Specifies which sysmessages error or message to alter.
WITH_LOG: Specifies that the sysmessages is to be written to the Windows NT event log when it occurs.
Note If a message is written to the Windows NT event log, it is also written to the SQL Server error log file.
true | false: Specifies whether the error will be written to the Windows NT event log. If true, the error will be automatically written to the Windows NT event log. If false, the error will not be automatically written to the Windows NT event log but may be written, depending on how the error was raised.

Remarks

The effect of the sp_altermessage system stored procedure with the WITH_LOG option is similar to that of the RAISERROR WITH LOG parameter, except that sp_altermessage changes the logging behavior of an existing message. If a message has been altered to be WITH_LOG, then no matter how a user invokes the error it will always be written to the Windows NT event log. Even if RAISERROR is executed without the WITH LOG option, the error will be written to the Windows NT event log.

System messages (such as 605), as well as user messages added by sp_addmessage, can be modified by using sp_altermessage.

Example

This example causes existing message 55001 to be logged to the Windows NT event log.

sp_altermessage 55001, WITH_LOG, TRUE

Permission

Execute permission defaults to the system administrator and can be transferred to other users.

Table Used

sysmessages