sp_dropmessage (Transact-SQL)

Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.

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.

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.

Permissions

Requires membership in the sysadmin and serveradmin fixed server roles.

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

Reference

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

Other Resources

Using RAISERROR

Help and Information

Getting SQL Server 2005 Assistance