sp_altermessage (Transact-SQL)
Alters the state of user-defined messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Important
This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.
.gif "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 cannot 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
See Also
Reference
RAISERROR (Transact-SQL)
sp_addmessage (Transact-SQL)
sp_dropmessage (Transact-SQL)
System Stored Procedures (Transact-SQL)