sp_altermessage
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 and serveradmin fixed server roles can execute this procedure.
Examples
This example causes existing message 55001 to be logged to the Windows NT application log.
sp_altermessage 55001, 'WITH_LOG', 'true'