sp_addmessage
Adds a new error message to the sysmessages table.
Syntax
sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
Arguments
[@msgnum =] msg_id
Is the ID of the message. msg_id is int, with a default of NULL. Acceptable values for user-defined error messages start with 50001. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.
[@severity =] severity
Is the severity level of the error. severity is smallint, with a default of NULL. Valid levels are from 1 through 25. Only the system administrator can add a message with a severity level from 19 through 25.
[@msgtext =] 'msg'
Is the text of the error message. msg is nvarchar(255), with a default of NULL.
[@lang =] 'language'
Is the language for this message. language is sysname, with a default of NULL. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.
[@with_log =] 'with_log'
Is whether the message is to be written to the Microsoft® Windows NT® application log when it occurs. with_log is varchar(5), with a default of FALSE. If true, the error is always written to the Windows NT application log. If false, the error is not always written to the Windows NT application log but can be written, depending on how the error was raised. Only members of the sysadmin server role can use this option.
Note If a message is written to the Windows NT application log, it is also written to the Microsoft SQL Server™ error log file.
[@replace =] 'replace'
If specified as the string REPLACE, an existing error message is overwritten with new message text and severity level. replace is varchar(7), with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
For localization, the U.S. English version of a message must already exist before the message in another language can be added. The severity of the messages must match.
When localizing messages that contain parameters, use parameter numbers that correspond to the parameters in the original message. Insert an exclamation point (!) after each parameter number.
| Original message | Localized message |
|---|---|
| 'Original message param 1: %s,
param 2: %d' |
'Localized message param 1: %1!,
param 2: %2!' |
Because of language syntax differences, the parameter numbers in the localized message may not occur in the same sequence as in the original message.
Permissions
Only members of the sysadmin and serveradmin fixed server roles can execute this procedure.
Examples
A. Define a custom message
This example adds a custom message to sysmessages.
USE master
EXEC sp_addmessage 50001, 16,
N'Percentage expects a value between 20 and 100.
Please reexecute with a more appropriate value.'
B. Add a message in two languages
This example first adds a message in U.S. English and then adds the same message in French.
USE master
EXEC sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = N'The item named %s already exists in %s.',
@lang = 'us_english'
EXEC sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = N'L''?l?ment nomm? %1! existe d?j? dans %2!',
@lang = 'French'