sp_dropmessage (Transact-SQL)
Applies to:
SQL Server
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 = ] msgnum ]
[ , [ @lang = ] N'lang' ]
[ ; ]
Arguments
[ @msgnum = ] msgnum
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).
[ @lang = ] N'lang'
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.
Return code values
0
(success) or 1
(failure).
Result set
None.
Permissions
Requires membership in the sysadmin and serveradmin fixed server roles.
Remarks
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.
Examples
A. Drop 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. Drop a user-defined message that includes a localized version
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
C. Drop 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
Related content
Tilbakemeldinger
https://aka.ms/ContentUserFeedback.
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se:Send inn og vis tilbakemelding for