sp_addmessage (Transact-SQL)sp_addmessage (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

將新的使用者自訂錯誤訊息儲存在 SQL Server Database EngineSQL Server Database Engine 的執行個體中。Stores a new user-defined error message in an instance of the SQL Server Database EngineSQL Server Database Engine. 使用儲存的訊息sp_addmessage可以使用檢視sys.messages目錄檢視。Messages stored by using sp_addmessage can be viewed by using the sys.messages catalog view.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'   
     [ , [ @lang= ] 'language' ]   
     [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]   
     [ , [ @replace= ] 'replace' ]   

引數Arguments

[ \@msgnum = ] msg_id 是訊息的識別碼。[ \@msgnum = ] msg_id Is the ID of the message. msg_idint預設值是 NULL。msg_id is int with a default of NULL. msg_id使用者定義錯誤訊息可以是 50,001 和 2,147,483,647 之間的整數。msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. 組合msg_id語言必須是唯一的; 如果指定的語言的識別碼已經存在,會傳回錯誤。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 是錯誤的嚴重性層級。[ \@severity = ]severity Is the severity level of the error. 嚴重性smallint預設值是 NULL。severity is smallint with a default of NULL. 有效的層級範圍是 1 到 25。Valid levels are from 1 through 25. 如需有關嚴重性的詳細資訊,請參閱 Database Engine 錯誤嚴重性For more information about severities, see Database Engine Error Severities.

[ \@msgtext = ] 'msg' 這是錯誤訊息的文字。[ \@msgtext = ] 'msg' Is the text of the error message. msgnvarchar(255) 預設值是 NULL。msg is nvarchar(255) with a default of NULL.

[ \@lang = ] 'language' 為此訊息的語言。[ \@lang = ] 'language' Is the language for this message. 語言sysname預設值是 NULL。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 = ] { 'TRUE' | 'FALSE' } 將訊息寫入 Windows 應用程式記錄檔,當它發生時。[ \@with_log = ] { 'TRUE' | 'FALSE' } Is whether the message is to be written to the Windows application log when it occurs. @with_logvarchar(5) 預設值是 FALSE。@with_log is varchar(5) with a default of FALSE. 如果是 TRUE,錯誤一律會寫入 Windows 應用程式記錄檔中。If TRUE, the error is always written to the Windows application log. 如果是 FALSE,錯誤就不一定會寫入 Windows 應用程式記錄檔中,但隨著錯誤的產生方式而不同,也可能會寫入。If FALSE, the error is not always written to the Windows application log but can be written, depending on how the error was raised. 只有成員sysadmin伺服器角色可以使用此選項。Only members of the sysadmin server role can use this option.

注意

如果訊息寫入 Windows 應用程式記錄檔中,它也會寫入 Database EngineDatabase Engine 錯誤記錄檔中。If a message is written to the Windows application log, it is also written to the Database EngineDatabase Engine error log file.

[ \@replace = ] 'replace' 如果指定為字串取代,新訊息文字和嚴重性層級覆寫現有的錯誤訊息。[ \@replace = ] 'replace' If specified as the string replace, an existing error message is overwritten with new message text and severity level. 取代varchar(7) 預設值是 NULL。replace is varchar(7) with a default of NULL. 必須指定此選項,如果msg_id已經存在。This option must be specified if msg_id already exists. 如果您取代 U.S.English 訊息,嚴重性層級會取代所有具有相同的其他語言中的所有訊息msg_idIf 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 (成功) 或 1 (失敗)0 (success) or 1 (failure)

結果集Result Sets

NoneNone

備註Remarks

對於非英文版本的SQL ServerSQL Server,美國訊息可以使用另一種語言加入之前,必須已經存在之訊息的英文版。For non-English versions of SQL ServerSQL Server, the U.S. English version of a message must already exist before the message can be added using another language. 兩個版本的訊息,嚴重性必須相符。The severity of the two versions of the message 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,'Original message param 1: %s,

param 2: %d'param 2: %d'
'Localized message param 1: %1!,'Localized message param 1: %1!,

param 2: %2!'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.

PermissionsPermissions

需要的成員資格sysadmin或是serveradmin固定伺服器角色。Requires membership in the sysadmin or serveradmin fixed server roles.

範例Examples

A.A. 定義自訂訊息Defining a custom message

下列範例會將自訂訊息sys.messagesThe following example adds a custom message to sys.messages.

USE master;  
GO  
EXEC sp_addmessage 50001, 16,   
   N'Percentage expects a value between 20 and 100.   
   Please reexecute with a more appropriate value.';  
GO  

B.B. 加入兩種語言的訊息Adding a message in two languages

下列範例首先會將訊息加入美國再加入法文的相同的訊息.The following example first adds a message in U.S. English and then adds the same message in French.

USE master;  
GO  
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';  
GO  

C.C. 變更參數的順序Changing the order of parameters

下列範例首先會將訊息加入美國英文、,然後將加入當地語系化的訊息中的參數順序會變更。The following example first adds a message in U.S. English, and then adds a localized message in which the parameter order is changed.

USE master;  
GO  
  
EXEC sp_addmessage   
    @msgnum = 60000,   
    @severity = 16,  
    @msgtext =   
        N'This is a test message with one numeric  
        parameter (%d), one string parameter (%s),   
        and another string parameter (%s).',  
    @lang = 'us_english';  
  
EXEC sp_addmessage   
    @msgnum = 60000,   
    @severity = 16,  
    @msgtext =   
        -- In the localized version of the message,  
        -- the parameter order has changed. The   
        -- string parameters are first and second  
        -- place in the message, and the numeric   
        -- parameter is third place.  
        N'Dies ist eine Testmeldung mit einem   
        Zeichenfolgenparameter (%3!),  
        einem weiteren Zeichenfolgenparameter (%2!),   
        und einem numerischen Parameter (%1!).',  
    @lang = 'German';  
GO    
  
-- Changing the session language to use the U.S. English  
-- version of the error message.  
SET LANGUAGE us_english;  
GO  
  
RAISERROR(60000,1,1,15,'param1','param2') -- error, severity, state,  
GO                                       -- parameters.  
  
-- Changing the session language to use the German  
-- version of the error message.  
SET LANGUAGE German;  
GO  
  
RAISERROR(60000,1,1,15,'param1','param2'); -- error, severity, state,   
GO                                       -- parameters.  

另請參閱See Also

RAISERROR (Transact-SQL) RAISERROR (Transact-SQL)
sp_altermessage (-SQL)) sp_altermessage (Transact-SQL)
sp_dropmessage (Transact-SQL) sp_dropmessage (Transact-SQL)
系統預存程序 (Transact-SQL)System Stored Procedures (Transact-SQL)