April 22, 2012

sp_dropmessage (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_dropmessage(int @msgnum
, nvarchar @lang)

MetaData:

 create procedure sys.sp_dropmessage  
@msgnum int = null, -- Number of message to drop.
@lang sysname = null -- Language of message to drop (or 'ALL')
as
declare @retcode int
declare @msglangid smallint

-- Must be ServerAdmin to manage messages
if is_srvrolemember('serveradmin') = 0
begin
raiserror(15247,-1,-1)
return (1)
end

-- If no message id, show usage
if @msgnum is null
begin
raiserror(15177,-1,-1)
return (1)
end

-- Message id must be > 50000 (unless SQL Server is in single user mode)
if @msgnum < 50001 AND 0 = SERVERPROPERTY('IsSingleUser')
begin
raiserror(15178,-1,-1)
return(1)
end

-- Verify the language
if @lang is null
select @lang = @@language
if upper(@lang) <> 'ALL'
begin
-- Check to see if this language is in Syslanguages.
if not exists (select * from sys.syslanguages where name = @lang or alias = @lang)
and @lang <> N'us_english'
begin
raiserror(15033,-1,-1,@lang)
return (1)
end

-- Get langid from syslanguages; us_english won't exist, so use 0.
select @msglangid = isnull((select msglangid from sys.syslanguages where name = @lang or alias = @lang),1033)
end

BEGIN TRANSACTION
EXEC %%ErrorMessage(ID = @msgnum).Lock(Exclusive = 1)
if (@@error <> 0) or -- msg or specified langugage version not found
(@msglangid is not null and not exists
(select * from sys.messages$ where message_id = @msgnum and language_id = @msglangid)
)
begin
COMMIT TRANSACTION
raiserror(15179,-1,-1,@msgnum)
return(1)
end

-- The us_english version must be the last one to be dropped
if (@msglangid = 1033) and (select count(*) from sys.messages$ where message_id = @msgnum) > 1
begin
COMMIT TRANSACTION
raiserror(15280,-1,-1)
return(1)
end

declare @msg_str nvarchar(50)
set @msg_str = @msgnum

-- Drop the message.
if (upper(@lang) = 'ALL') or (@msglangid = 1033)
EXEC %%ErrorMessage(ID = @msgnum).Remove()
else
EXEC %%ErrorMessage(ID = @msgnum).RemoveMessage(LanguageID = @msglangid)


-- EMDEventType(x_eet_Drop_Message), EMDUniversalClass(x_eunc_Message), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 237, ID = 106, ID = @msgnum, ID = 0, Value = @msg_str,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @msgnum, Value = @lang, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

COMMIT TRANSACTION

return (0) -- sp_dropmessage

No comments:

Post a Comment

Total Pageviews