sp_altermessage (T-SQL)

Alters the state of a sysmessages error.

Syntax

sp_altermessage [@message_id =] message_number,
[@parameter =] 'write_to_log',
[@parameter_value =] 'value'

Arguments

[@message_id =] message_number: Is the sysmessages error or message number to alter. message_number is int, with no default.
[@parameter =] 'write_to_log': Indicates that the message is written to the Microsoft® Windows NT® application log. write_to_log is sysname, with no default value. If write_to_log is WITH_LOG, the message is written to the Microsoft Windows NT log when it occurs.

Note If a message is written to the Windows NT application log, it is also written to the Microsoft SQL Server™ error log file.
[@parameter_value =] 'value': Is whether the error is written to the Windows NT application log. value is varchar(5), with no default. If true, the error is always written to the Windows NT application log. If false, the error is not always written to the Windows NT application log but can be written, depending on how the error was raised.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

The effect of sp_altermessage 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, it is always written to the Windows NT application log, regardless of how a user invokes the error. Even if RAISERROR is executed without the WITH LOG option, the error is written to the Windows NT application log.

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

Permissions

Only members of the sysadmin fixed server role can execute sp_altermessage.

Examples

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

sp_altermessage 55001, 'WITH_LOG', 'true'