sp_altermessage (Transact-SQL)

Article
05/16/2013

Alters the state of user-defined or system messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[@message_id = ] message_number
Is the error number of the message to alter from sys.messages. message_number is int with no default value.
[ @parameter = ] **'**write_to_log'
Is used with @parameter_value to indicate that the message is to be written to the Microsoft Windows application log. write_to_log is sysname with no default value. write_to_log must be set to WITH_LOG or NULL. If write_to_log is set to WITH_LOG or NULL, and the value for @parameter_value is true, the message is written to the Windows application log. If write_to_log is set to WITH_LOG or NULL and the value for @parameter_value is false, the message is not always written to the Windows application log, but may be written depending upon how the error was raised. If write_to_log is specified, the value for @parameter_value must also be specified.

Note

If a message is written to the Windows application log, it is also written to the Database Engine error log file.
[ @parameter_value = ]**'**value'
Is used with @parameter to indicate that the error is to be written to the Microsoft Windows application log. value is varchar(5), with no default value. If true, the error is always written to the Windows application log. If false, the error is not always written to the Windows application log, but may be written depending upon how the error was raised. If value is specified, write_to_log for @parameter must also be specified.

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 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 application log.

System messages can be modified by using sp_altermessage.

Permissions

Requires membership in the serveradmin fixed server role.

Examples

The following example causes existing message 55001 to be logged to the Windows application log.

sp_altermessage 55001, 'WITH_LOG', 'true';
GO

Share via