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