sp-dropmessage-transact-sql.md

title	sp_dropmessage (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	06/10/2016
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_dropmessage_TSQL sp_dropmessage
dev_langs	TSQL
helpviewer_keywords	sp_dropmessage
ms.assetid	17287a15-cdde-43d1-bb18-9f920bc15db8
caps.latest.revision	32
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sp_dropmessage (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Drops a specified user-defined error message from an instance of the [!INCLUDEssDEnoversion]. User-defined messages can be viewed using the sys.messages catalog view.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Transact-SQL Syntax Conventions

Syntax

  
sp_dropmessage [ @msgnum = ] message_number  
    [ , [ @lang = ] 'language' ]  

Arguments

[ @msgnum = ] message_number
Is the message number to drop. message_number must be a user-defined message that has a message number greater than 50000. message_number is int, with a default of NULL.

[ @lang = ] 'language'
Is the language of the message to drop. If all is specified, all language versions of message_number are dropped. language is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None.

Permissions

Requires membership in the sysadmin and serveradmin fixed server roles.

Remarks

Unless all is specified for language, all localized versions of a message must be dropped before the U.S. English version of the message can be dropped.

Examples

A. Dropping a user-defined message

The following example drops a user-defined message, number 50001, from sys.messages.

USE master;  
GO  
EXEC sp_dropmessage 50001;  

B. Dropping a user-defined message that includes a localized version

The following example drops a user-defined message, number 60000, that includes a localized version of the message.

USE master;  
GO  
  
-- Create a user-defined message in U.S. English  
EXEC sp_addmessage   
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'The item named %s already exists in %s.',   
    @lang = 'us_english';  
  
-- Create a localized version of the same message.  
EXEC sp_addmessage   
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'L''élément nommé %1! existe déjà dans %2!',  
    @lang = 'French';  
GO  
  
-- This statement will fail as long as the localized version  
-- of the message exists.  
EXEC sp_dropmessage 60000;  
GO  
  
-- This statement will drop the message.  
EXEC sp_dropmessage  
    @msgnum = 60000,  
    @lang = 'all';  
GO  

C. Dropping a localized version of a user-defined message

The following example drops a localized version of a user-defined message, number 60000, without dropping the whole message.

USE master;  
GO  
  
-- Create a user-defined message in U.S. English  
EXEC sp_addmessage   
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'The item named %s already exists in %s.',   
    @lang = 'us_english';  
  
-- Create a localized version of the same message.  
EXEC sp_addmessage   
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'L''élément nommé %1! existe déjà dans %2!',  
    @lang = 'French';  
GO  
-- This statement will remove only the localized version of the   
-- message.  
EXEC sp_dropmessage  
    @msgnum = 60000,  
    @lang = 'French';  
GO  

See Also

RAISERROR (Transact-SQL)
sp_addmessage (Transact-SQL)
sp_altermessage (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
sys.messages (Transact-SQL)
System Stored Procedures (Transact-SQL)