title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_dropmessage (Transact-SQL) |
Drops a specified user-defined error message from an instance of the Database Engine. |
markingmyname |
maghan |
randolphwest |
11/28/2023 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Drops a specified user-defined error message from an instance of the [!INCLUDE ssDEnoversion]. User-defined messages can be viewed using the sys.messages
catalog view.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_dropmessage
[ [ @msgnum = ] msgnum ]
[ , [ @lang = ] N'lang' ]
[ ; ]
The message number to drop. @msgnum is int, with a default of NULL
. @msgnum must be a user-defined message that's a message number greater than 50000
(50,000).
The language of the message to drop. @lang is sysname, with a default of NULL
. If all
is specified, all language versions of @msgnum are dropped.
0
(success) or 1
(failure).
None.
Requires membership in the sysadmin and serveradmin fixed server roles.
Unless all
is specified for @lang, all localized versions of a message must be dropped before the U.S. English version of the message can be dropped.
The following example drops a user-defined message, number 50001
, from sys.messages
.
USE master;
GO
EXEC sp_dropmessage 50001;
The following example adds a user-defined message, number 60000
, which includes a localized version, then drops both variations 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
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