May 10, 2012

sp_MSdelsubrowsbatch (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_MSdelsubrowsbatch(int @tablenick
, varbinary @rowguid_array
, varbinary @metadatatype_array
, varbinary @oldlineage_len_array
, image @oldlineage_array
, varbinary @generation_array
, varbinary @newlineage_len_array
, image @newlineage_array
, uniqueidentifier @pubid
, bit @allarticlesareupdateable)

MetaData:

 create procedure sys.sp_MSdelsubrowsbatch   
(@tablenick int,
@rowguid_array varbinary(8000),
@metadatatype_array varbinary(500), -- 0 - Missing, 1 - Tombstone, 2 - Contents, 3 - ContentsDeferred, 6 - system delete
@oldlineage_len_array varbinary(1000), -- specifies the number of lineages in the @oldlineage_array.
@oldlineage_array image,
@generation_array varbinary(4000), -- MAX_ROWS_FOR_BATCHEDSUBDELETES*sizeof(GENERATION)
@newlineage_len_array varbinary(1000), -- specifies the number of lineages in the @newlineage_array.
@newlineage_array image,
@pubid uniqueidentifier = NULL,
@rowsdeleted INT = NULL OUTPUT,
@allarticlesareupdateable bit= 1)
as
declare @rowguid uniqueidentifier, @metadata_type tinyint, @lineage_old varbinary(311),
@generation bigint, @lineage_new varbinary(311), @match int, @errcode int, @new_metatype tinyint,
@retcode smallint, @procname sysname, @tnstr nvarchar(11), @error int, @rowcount int,
@tablenicklast int, @rowguidarraylen int, @oldlineage_len smallint, @newlineage_len smallint,
@guidoffset int, @metatypeoffset int, @oldlinlenoffset int, @newlinlenoffset int, @oldlinoffset int,
@newlinoffset int, @genoffset int, @transaction_started bit,
@parentarticleisupdateable bit, @partition_options tinyint

declare @rowstochangetype TABLE (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL)

declare @METADATA_TYPE_Tombstone tinyint
declare @METADATA_TYPE_PartialDelete tinyint
declare @METADATA_TYPE_SystemDelete tinyint

set @METADATA_TYPE_Tombstone= 1
set @METADATA_TYPE_PartialDelete= 5
set @METADATA_TYPE_SystemDelete= 6

-- Do all DDL first
create table #notbelong ( tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL, flag int NOT NULL, generation bigint NULL,
lineage_old varbinary(311) NULL, metadatatype_old tinyint NULL,
lineage_new varbinary(311) NULL, metadatatype_new tinyint NULL, original_row bit NULL default 0)
create clustered index #indnbelong on #notbelong (tablenick, rowguid)

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

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

if (1 = @allarticlesareupdateable and
@newlineage_array is null)
begin
RAISERROR(14043, 16, -1, '@newlineage_array', 'sp_MSdelsubrowsbatch')
return (0)
end

set @rowsdeleted = 0
set @transaction_started = 0

-- initialize offsets and length for walking through arrays
set @guidoffset = 1
set @metatypeoffset = 1
set @oldlinlenoffset = 1
set @newlinlenoffset = 1
set @oldlinoffset = 1
set @newlinoffset = 1
set @genoffset = 1

set @rowguidarraylen = datalength(@rowguid_array)

declare @numgenbytes tinyint
set @numgenbytes= col_length('MSmerge_contents', 'generation')

if 1 = @allarticlesareupdateable
set @parentarticleisupdateable= 1
else
set @parentarticleisupdateable= sys.fn_MSarticle_allows_DML_at_this_replica(default, @tablenick)

-- walk through arrays and populate temp table
while (@guidoffset < @rowguidarraylen)
begin
-- Retrieve values, and increment offsets for next row.
set @rowguid = substring(@rowguid_array, @guidoffset, 16) -- 16 = sizeof uniqueidentifier (rowguid)
set @guidoffset = @guidoffset + 16

set @metadata_type = substring(@metadatatype_array, @metatypeoffset, 1) -- 1 = sizeof tinyint (metadata_type)
set @generation = substring(@generation_array, @genoffset, @numgenbytes)
set @oldlineage_len = substring(@oldlineage_len_array, @oldlinlenoffset, 2) -- 2 = sizeof smallint (oldlineage_len)
set @newlineage_len = substring(@newlineage_len_array, @newlinlenoffset, 2) -- 2 = sizeof smallint (newlineage_len)
set @lineage_old = substring(@oldlineage_array, @oldlinoffset, @oldlineage_len) -- @oldlineage_len = sizeof old lineage for current row
set @lineage_new = substring(@newlineage_array, @newlinoffset, @newlineage_len) -- @newlineage_len = sizeof new lineage for current row

set @metatypeoffset = @metatypeoffset + 1
set @genoffset = @genoffset + @numgenbytes
set @oldlinlenoffset = @oldlinlenoffset + 2
set @newlinlenoffset = @newlinlenoffset + 2
set @oldlinoffset = @oldlinoffset + @oldlineage_len
set @newlinoffset = @newlinoffset + @newlineage_len

-- Insert the old metadata type as the new metadata type. We can modify later if it is supposed to be different.
insert into #notbelong (tablenick, rowguid, flag, generation, lineage_old, metadatatype_old, lineage_new, metadatatype_new, original_row) values (@tablenick, @rowguid, 0, @generation, @lineage_old, @metadata_type, @lineage_new, @metadata_type, 1)
end

if (exists(select * from #notbelong))
begin
declare @tn int
declare @qualified_table_name nvarchar(517)
declare @unqualified_table_name nvarchar(270)

-- Expansion is an expensive and time-consuming process. Defer starting transaction until after expansion
-- has taken place.

-- call expand proc --
exec @retcode = sys.sp_MSexpandsubsnb @pubid
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
goto Failure
end

select @procname = 'dbo.' + select_proc,
@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

-- After the #notbelong has been expanded, the original_row column can be used to distinguish original rows
-- from the rows that were brought in by the expansion.

-- open a cursor on #notbelong for rows with original_row = 1
declare original_rows_1 CURSOR LOCAL FAST_FORWARD for
select rowguid, generation, lineage_old, metadatatype_old, lineage_new from #notbelong where tablenick = @tablenick and original_row = 1
FOR READ ONLY
open original_rows_1
fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new

begin transaction
save tran start_of_batch
set @transaction_started = 1

while (@@fetch_status <> -1)
begin
if 1 = @parentarticleisupdateable
begin
if (@metadata_type = @METADATA_TYPE_PartialDelete or
@metadata_type = @METADATA_TYPE_Tombstone)
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


-- This row will later be removed from #notbelong. We were only supposed to update the tombstone
-- metadata type for this row (which we have already done above).
insert into @rowstochangetype values (@tablenick, @rowguid)
fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new
continue -- on to the next row
end
end
else if (@metadata_type = @METADATA_TYPE_SystemDelete)
begin
if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick)
begin
update dbo.MSmerge_tombstone set type = @metadata_type
where rowguid = @rowguid and tablenick = @tablenick


-- This row will later be removed from #notbelong. We were only supposed to update the tombstone
-- metadata type for this row (which we have already done above).
insert into @rowstochangetype values (@tablenick, @rowguid)
fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new
continue -- on to the next row
end
end
end

-- lock this particular row of the base table
exec @retcode = @procname @maxschemaguidforarticle = NULL, @type = 8, @rowguid=@rowguid
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
close original_rows_1
deallocate original_rows_1
goto Failure
end

if 1 = @parentarticleisupdateable
begin
if @metadata_type = @METADATA_TYPE_PartialDelete
begin
set @new_metatype = @METADATA_TYPE_PartialDelete
end
else if @metadata_type = @METADATA_TYPE_SystemDelete
begin
set @new_metatype = @METADATA_TYPE_SystemDelete
end
else
begin
set @new_metatype = @METADATA_TYPE_Tombstone
end

-- call sp_MScheckmetadatamatch with @compatlevel=90, because even if the
-- reconciler is Shiloh, the lineages were already mapped up when the
-- deletes where enumerated, and the reconciler then
-- concatenated the mapped-up lineages
exec @retcode=sys.sp_MScheckmetadatamatch
@metatype=@metadata_type, @rowguid=@rowguid, @tablenick=@tablenick,
@lineage=@lineage_old, @match=@match output, @compatlevel=90

IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
close original_rows_1
deallocate original_rows_1
goto Failure
end
end
else
set @match= 1

if (@match = 1)
begin
if (@metadata_type <> @new_metatype and 1 = @parentarticleisupdateable)
begin
-- we will later need this new metadata type when calling sp_MSsetrowmetadata
update #notbelong set metadatatype_new = @new_metatype where tablenick = @tablenick and rowguid = @rowguid and original_row = 1
end
end
else
begin
set @errcode= 2
close original_rows_1
deallocate original_rows_1
goto Failure
end

fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new
end

close original_rows_1
deallocate original_rows_1

-- delete the rows in #notbelong that needn't be deleted. We were only supposed to update the tombstone
-- metadata type for those rows (which we have already done above).
delete #notbelong with (paglock) from #notbelong a, @rowstochangetype b where a.tablenick = b.tablenick and a.rowguid = b.rowguid

select @tn = max(tablenick) from #notbelong where flag > -1
while @tn is not null
begin
select @tnstr = convert(nvarchar(11), @tn)
exec @retcode = sys.sp_MStablenamefromnick @tn, @qualified_table_name out, @pubid, @unqualified_table_name out
-- delete all rows indicated by the temp table
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
goto Failure
end
exec ('delete ' + @qualified_table_name + ' from #notbelong nb, ' + @qualified_table_name + ' t
where nb.tablenick = '
+ @tnstr + ' and t.RowGuidCol = nb.rowguid
option (FORCE ORDER, LOOP JOIN)'
)
select @error=@@error, @rowcount=@@rowcount
IF @error<>0
begin
set @errcode= 0
goto Failure
end

select @rowsdeleted = @rowsdeleted + @rowcount

-- remove metadata action request for the rows we just deleted.
delete mar with (rowlock) from dbo.MSmerge_metadataaction_request as mar
inner join #notbelong as nb
on mar.tablenick=nb.tablenick and
mar.rowguid=nb.rowguid
where mar.tablenick=@tn

-- move on to next nickname - decreasing makes delete order correct
update #notbelong set flag = -1 where tablenick = @tn
select @tn = max(tablenick) from #notbelong where flag > -1
end

-- change tombstone type for the non-original rows (the rows that got deleted via expansion).
update dbo.MSmerge_tombstone
set type = @METADATA_TYPE_PartialDelete
from
(select distinct tablenick, rowguid from
#notbelong where original_row <> 1) nb,
dbo.MSmerge_tombstone ts
where ts.tablenick = nb.tablenick and
ts.rowguid = nb.rowguid
option (FORCE ORDER, LOOP JOIN)

-- open a cursor and get the new metadata types for the original rows and then call sp_MSsetrowmetadata for each original row.
declare original_rows_2 CURSOR LOCAL FAST_FORWARD for
select tablenick, rowguid, generation, lineage_new, metadatatype_new from #notbelong where original_row = 1
FOR READ ONLY
open original_rows_2
fetch original_rows_2 into @tablenick, @rowguid, @generation, @lineage_new, @new_metatype
while (@@fetch_status <> -1)
begin
if 1 = @parentarticleisupdateable and (@partition_options < 2 or @lineage_new is not null)
begin
-- Call sp_MSsetrowmetadata for only the original rows whose delete requests were passed in to this proc.
-- call it with @compatlevel=90, because even if the reconciler is Shiloh, the lineages
-- were already mapped up when the deletes where enumerated, and the reconciler then
-- concatenated the mapped-up lineages
exec @retcode= sys.sp_MSsetrowmetadata
@tablenick, @rowguid, @generation,
@lineage_new, NULL, @new_metatype, NULL, 90
IF @@ERROR<>0 or @retcode<>0
begin
set @errcode= 0
close original_rows_2
deallocate original_rows_2
goto Failure
end
end
fetch original_rows_2 into @tablenick, @rowguid, @generation, @lineage_new, @new_metatype
end
close original_rows_2
deallocate original_rows_2

commit tran

end


drop table #notbelong

return 1 -- in sp_MSdelsubrows, 1=okay

Failure:
-- instead of checking @@trancount, check our bit flag. This is safer as we can rely on it whether or not we are called
-- from an outer transaction.
if (@transaction_started = 1)
begin
rollback tran start_of_batch
commit tran
end


drop table #notbelong

if @errcode = 1
set @errcode = 0

return(@errcode) -- in sp_MSdelsubrows, 0=error

No comments:

Post a Comment

Total Pageviews