sp_addmessage System Stored Procedure

Adds a new error message to the sysmessages table.

Syntax

sp_addmessage msg_id, severity, 'text of message'
[, language [, {true | false} [, REPLACE]]]

where

msg_id: Is the ID of the message. It must be an integer. Acceptable values for user-defined error messages start with 50001. The msg_id must be unique; an error will be returned if that ID already exists. By default, any message added will have the language ID (@@langid) of the connection adding the message. To add a message for a different language, first use the SET LANGUAGE statement.
severity: Represents the severity level of the error. Valid levels are 1 through 25. Only the system administrator can add a message with a severity level of 19 through 25. For details on severity levels and use, see the Microsoft SQL Server Administrator's Companion.
text of message: Is the text of the error message. The string can have as many as 255 characters and must be surrounded by single quotation marks.
language: Specifies the language for this message. Because multiple languages can be installed on the same server, language specifies in which language each message is written. When not specified, U.S. English is the default language.
true | false: Specifies whether the sysmessages is to be written to the Windows NT event log when it occurs. If true, the error will be automatically written to the Windows NT event log. If false, the error will not be automatically written to the Windows NT event log but may be written, depending on how the error was raised.
Note If a message is written to the Windows NT event log, it is also written to the SQL Server error log file.
REPLACE: Is optionally used to overwrite an existing error message with new message text and severity level. This option must be specified if the msg_id already exists.

Remarks

If no parameters are supplied, a syntax diagram will be displayed.

Example

This example adds an informational message to sysmessages.

sp_addmessage 50001, 16, 'Percentage expects a value between 20 and 100. Please re-execute with a more appropriate value.'

Permission

Execute permission defaults to the system administrator, who can grant execute permission to other users.

Tables Used

sysmessages