Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.
sp_addmessage message_num, message_text [, language [, with_log [, replace]]]
is the message number of the message to add. The message number for a user-defined message must be 20000 or greater.
is the text of the message to add. The maximum length is 1024 bytes.
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.
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.
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.
Adds a message with the number 20001 to sysusermessages:
sp_addmessage 20001, "The table '%1!' is not owned by the user '%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"
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.
Only the user who created a message can execute sp_addmessage with the replace option to replace that original message.
The permission checks for sp_addmessage differ based on your granular permissions settings.
Granular permissions 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. |
Granular permissions 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. |
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 |
|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_altermessage, sp_bindmsg, sp_dropmessage, sp_getmessage
