sp_addmessage

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, the SAP ASE server assumes that messages are in the default session language indicated by @@langid.
with_log – specifies whether the message is logged in the SAP ASE error log as well as in the Windows Event Log on Windows 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 is not 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 SAP ASE error log, as well as in the Windows Event Log on Windows 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 the SAP ASE 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.

Only the user who created a message can execute sp_addmessage with the replace option to replace that original message.

See also print, raiserror in Reference Manual: Commands.

Permissions

The permission checks for sp_addmessage differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, any user can execute sp_addmessage. To add a message with with_log, you must be the database owner or a user with `own database` privilege on the database.
Disabled	With granular permissions disabled, any user can execute sp_addmessage. To add a message with with_log, you must be the database owner or a user with sa_role. Only the user who created the 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:

Information	Values
Event	15
Audit option	create
Command or access audited	sp_addmessage
Information in `extrainfo`	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