Chapter 1: System Procedures

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

sp_addmessage does not overwrite an existing message of the same number and langid unless you specify @replace = “replace".
print and raiserror recognize placeholders in the message text to print out. A single message can contain up to 20 unique placeholders in any order. These placeholders are replaced with the formatted contents of any arguments that follow the message when the text of the message is sent to the client.

The placeholders are numbered to allow reordering of the arguments when Adaptive Server is translating a message to a language with a different grammatical structure. A placeholder for an argument appears as “%nn!”, a percent sign (%), followed by an integer from 1 to 20, followed by an exclamation point (!). The integer represents the argument number in the string in the argument list. “%1!” is the first argument in the original version, “%2!” is the second argument, and so on.

Permissions

Any user can execute sp_addmessage.

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