sp_addmessage

Description

Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.

Syntax

sp_addmessage message_num, message_text 
	[, language [, with_log [, replace]]]

Parameters

message_num

is the message number of the message to add. The message number for a user-defined message must be 20000 or greater.

message_text

is the text of the message to add. The maximum length is 1024 bytes.

language

is the language of the message to add. This must be a valid language name in the syslanguages table. If this parameter is missing, Adaptive Server assumes that messages are in the default session language indicated by @@langid.

with_log

specifies whether the message is logged in the Adaptive Server error log as well as in the Windows NT Event Log on Windows NT servers, if logging is enabled. If with_log is TRUE, the message is logged, regardless of the severity of the error. If with_log is FALSE, the message may or may not be logged, depending on the severity of the error. If you do not specify a value for with_log, the default is FALSE.

replace

specifies whether to overwrite an existing message of the same number and languid. If replace is specified, the existing message is overwritten; if replace is omitted, it is not. If you do not specify a value for replace, the parameter’s default behavior specifies that the existing message will not be overwritten.

Examples

Example 1

Adds a message with the number 20001 to sysusermessages:

sp_addmessage 20001, "The table '%1!' is not owned by the user '%2!'."

Example 2

Adds a message with the number 20002 to sysusermessages. This message is logged in the Adaptive Server error log, as well as in the Windows NT Event Log on Windows NT servers, if event logging is enabled. If a message numbered 20002 exists in the default session language, this message overwrites the old message:

sp_addmessage 20002, "The procedure'%1!' is not owned
by the user '%2!'.", NULL, TRUE, "replace"

Usage

Permissions

Any user can execute sp_addmessage. However, only the user who created a message can execute sp_addmessage with the replace option to replace that original message.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

15

create

sp_addmessage

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Message number

  • Proxy information – Original login name, if set proxy in effect

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

See also

Commands print, raiserror

System procedures sp_altermessage, sp_bindmsg, sp_dropmessage, sp_getmessage