Export (0) Print
Expand All
Expand Minimize

sp_dropmessage (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

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

[ @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.

0 (success) or 1 (failure)

Requires membership in the sysadmin and serveradmin fixed server roles.

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.

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft