May 10, 2012

sp_MSdelrow (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_MSdelrow(uniqueidentifier @rowguid
, int @tablenick
, tinyint @metadata_type
, varbinary @lineage_old
, bigint @generation
, varbinary @lineage_new
, uniqueidentifier @pubid
, int @check_permission
, int @compatlevel
, bit @articleisupdateable
, smallint @publication_number
, int @partition_id)

MetaData:

   
create procedure sys.sp_MSdelrow
(@rowguid uniqueidentifier,
@tablenick int,
@metadata_type tinyint, -- 0 - Missing, 1 - Tombstone, 2 - Contents, 3 - ContentsDeferred, 6 - system delete --
@lineage_old varbinary(311),
@generation bigint,
@lineage_new varbinary(311),
@pubid uniqueidentifier = NULL,
@check_permission int = 0,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@articleisupdateable bit = 1,
@publication_number smallint = NULL,
@partition_id int = NULL
)
as
set nocount on
declare @match int
declare @new_metatype tinyint
declare @retcode smallint
declare @errcode int
declare @procname sysname
declare @objid int
declare @permissions int
declare @logical_record_parent_nickname int
declare @partition_options tinyint

-- Parameter validation --
if (@rowguid is null)
begin
RAISERROR(14043, 16, -1, '@rowguid', 'sp_MSdelrow')
return (0)
end
if (@tablenick is null)
begin
RAISERROR(14043, 16, -1, '@tablenick', 'sp_MSdelrow')
return (0)
end

--
-- Check to see if current publication has permission
--
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick
if (@retcode <> 0) or (@@error <> 0)
return 4

if @check_permission =1
begin
select @objid=objid, @permissions= check_permissions from dbo.sysmergearticles
where nickname=@tablenick and (pubid is NULL or pubid=@pubid)
if @objid is NULL
return (0)

exec @retcode = sys.sp_MSreplcheck_permission @objid = @objid, @type = 3, @permissions = @permissions
if @retcode<>0 or @@ERROR<>0 return (4)
end

if 1 = @articleisupdateable and @lineage_new is not null
begin
if @compatlevel < 90
begin
set @lineage_new= {fn LINEAGE_80_TO_90(@lineage_new)}
if @lineage_old is not null
set @lineage_old= {fn LINEAGE_80_TO_90(@lineage_old)}
end

-- Are we just changing the type of a tombstone?
-- This routine is only called for Upload; won't be type 5 (remove from partial) unless
-- subscriber has a user delete and found an existing metadata type of 5 here.
-- In that case, set delete type to 1 and update generation, reason text too.
if (@metadata_type = 5)
begin
if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick)
begin
update dbo.MSmerge_tombstone set type = @metadata_type, generation = @generation, lineage = @lineage_new where
rowguid = @rowguid and tablenick = @tablenick
return 1
end
end
-- Are we just changing the type of a tombstone?
else if (@metadata_type = 6)
begin
if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick)
begin
update dbo.MSmerge_tombstone set type = @metadata_type,
generation = @generation, lineage = @lineage_new where
rowguid = @rowguid and tablenick = @tablenick
return 1
end
end
end

-- begin transaction and lock row that we plan to delete
begin transaction
save tran sp_MSdelrow

declare @rowcount int

select @procname = 'dbo.' + select_proc, @logical_record_parent_nickname = logical_record_parent_nickname,
@partition_options = partition_options
from dbo.sysmergepartitioninfoview
where nickname = @tablenick
and pubid = @pubid

if @partition_options = 2
begin
-- if this is a republisher of this article, and we are currently
-- downloading from the top-level publisher, then pretend that this is
-- not a well-partitioned article. This is done such that the partition evaluation
-- and setrowmetadata is done appropriately.
if sys.fn_MSmerge_islocalpubid(@pubid) = 0
and exists (select * from dbo.sysmergearticles
where nickname = @tablenick
and sys.fn_MSmerge_islocalpubid(pubid) = 1)
select @partition_options = 0
end

--
exec @retcode = @procname @maxschemaguidforarticle = NULL, @type =8, @rowguid=@rowguid
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
goto Failure
end
--


if 1 = @articleisupdateable
begin
select @match = NULL

if @metadata_type = 5
begin
set @new_metatype = 5
end
else if @metadata_type = 6
begin
set @new_metatype = 6
end
else
begin
--
-- call sp_MScheckmetadatamatch with @compatlevel=90, because sp_MSdelrow already
-- did the map-up if needed
exec @retcode=sys.sp_MScheckmetadatamatch
@metatype=@metadata_type, @rowguid=@rowguid, @tablenick=@tablenick,
@lineage=@lineage_old, @match=@match output, @compatlevel=90, @lineage_new=@lineage_new,
@new_type_contents=0
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
goto Failure
end
--

if @logical_record_parent_nickname = @tablenick
select @match = 1

set @new_metatype = 1
end
end
else
begin
set @match= 1
end

if (@match = 1)
begin
-- select_proc makes a delete with @type = 5, despite its name.
-- we need not hold any lock on the row. Instead we will detect that if we could not delete the row
-- we will raise an error
exec @retcode = @procname
NULL, -- @maxschemaguidforarticle = NULL --
11, -- @type
@rowguid, -- @rowguid
1, -- @enumentirerowmetadata not used
0, -- @blob_cols_at_the_end not used
'00000000-0000-0000-0000-000000000000', -- @logical_record_parent_rowguid not used
0, -- @metadata_type
NULL, -- @lineage_old
@rowcount output
if @retcode<>0
begin
set @errcode= 0
goto Failure
end

if @rowcount <> 1
begin
set @errcode= 3
goto Failure
end
end
else if @match is NULL
begin
-- we need to do a metadatacheck in the delete proc
exec @retcode = @procname
NULL, -- @maxschemaguidforarticle = NULL --
11, -- @type
@rowguid, -- @rowguid
1, -- @enumentirerowmetadata not used
0, -- @blob_cols_at_the_end not used
'00000000-0000-0000-0000-000000000000', -- @logical_record_parent_rowguid not used
@metadata_type, -- @metadata_type
@lineage_old, -- @lineage_old
@rowcount output
if @retcode<>0
begin
set @errcode= 0
goto Failure
end

if @rowcount <> 1
begin
set @errcode= 2
goto Failure
end
end
if @@error<>0
begin
set @errcode= 0
goto Failure
end

if 1 = @articleisupdateable and (@partition_options < 2 or @lineage_new is not null)
begin
-- call sp_MSsetrowmetadata with @compatlevel=90, because sp_MSdelrow
-- already did the map-up if it is needed
exec @retcode= sys.sp_MSsetrowmetadata
@tablenick, @rowguid, @generation,
@lineage_new, NULL, @new_metatype, NULL, 90,
@publication_number = @publication_number, @partition_id = @partition_id,
@partition_options = @partition_options

IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
goto Failure
end
end

commit tran
return(1)-- in sp_MSdelrow, 1=okay

Failure:
rollback tran sp_MSdelrow
commit tran
return(@errcode)

No comments:

Post a Comment

Total Pageviews