Chapter 1: System Procedures

sp_altermessage

Description

Enables and disables the logging of a system-defined or user-defined message in the Adaptive Server error log.

Syntax

sp_altermessage message_id, parameter, parameter_value

Parameters

message_id: is the message number of the message to be altered. This is the number of the message as it is recorded in the error column in the sysmessages or sysusermessages system table.

parameter: is the message parameter to be altered. The maximum length is 30 bytes. The only valid parameter is with_log.

parameter_value: is the new value for the parameter specified in parameter. The maximum length is 5 bytes. Values are true and false.

Examples

Example 1

Specifies that message number 2000 in sysmessages should be logged in the Adaptive Server error log and also in the Windows NT Event Log (if logging is enabled):

sp_altermessage 2000, 'with_log', 'TRUE'

Usage

If the parameter_value is true, the specified message is always logged. If it is false, the default logging behavior is used; the message may or may not be logged, depending on the severity of the error and other factors. Setting the parameter_value to false produces the same behavior that would occur if sp_altermessage had not been called.
On Windows NT servers, sp_altermessage also enables and disables logging in the Windows NT Event Log.

Permissions

Only the Database Owner or a System Administrator can execute sp_altermessage.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect