May 2, 2012

sp_mergedummyupdate (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_mergedummyupdate(nvarchar @source_object
, uniqueidentifier @rowguid)

MetaData:

   
create procedure sys.sp_mergedummyupdate(
@source_object nvarchar (386),
@rowguid uniqueidentifier
)
as
-- Security check
if 1 <> is_member('db_owner')
begin
RAISERROR (15247, 11, -1)
return (1)
end

declare @object sysname
declare @owner sysname
declare @tablenick int
declare @tablenickstr nvarchar(11)
declare @retcode int
declare @islightweight bit
declare @coltracked bit

select @object = PARSENAME(@source_object, 1)
select @owner = PARSENAME(@source_object, 2)
execute sys.sp_MStablenickname @owner, @object, @tablenick output
if @tablenick IS NULL or @@ERROR<>0
BEGIN
raiserror (20003, 11, -1, @object)
RETURN (1)
END
set @tablenickstr = convert(nchar, @tablenick)

declare @rowguidstr nvarchar(40)
if @rowguid IS NULL
begin
raiserror (14043, 11, -1, '@rowguid', 'sp_mergedummyupdate')
return (1)
end
set @rowguidstr = convert(nchar(36), @rowguid)

-- Find out whether the row is heavy- or lightweight.
select top 1 @islightweight= lightweight, @coltracked= column_tracking
from dbo.sysmergearticles where nickname=@tablenick
if @islightweight is null return 1

if 0=@islightweight
begin
declare @quoted_source_object nvarchar(517)
if @owner is null
set @quoted_source_object= quotename(@object)
else
set @quoted_source_object= quotename(@owner) + '.' + quotename(@object)

--
-- If the row does not exist in the base table, call sp_MSdummyupdate with metadata_type = 1 (tombstone)
--
exec ('if not exists (select 1 from ' + @quoted_source_object + ' where rowguidcol = ''' +
@rowguidstr + ''' ) exec sys.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 1')

--
-- If the row exists in the base table and MSmerge_contents , call sp_MSdummyupdate with metadata_type = 2 (contents)
--
exec ('if exists (select 1 from ' + @quoted_source_object + ' where rowguidcol = ''' +
@rowguidstr + ''' ) and exists (select rowguid from dbo.MSmerge_contents where rowguid = ''' +
@rowguidstr + ''' ) exec sys.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 2')

--
-- If the row exists in the base table, but not in MSmerge_contents , call sp_MSdummyupdate with metadata_type = 3 (contents_deferred)
--
exec ('if exists (select 1 from ' + @quoted_source_object + ' where rowguidcol = ''' +
@rowguidstr + ''' ) and not exists (select rowguid from dbo.MSmerge_contents where rowguid = ''' +
@rowguidstr + ''' ) exec sys.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 3')
end
else
begin
declare @metatype tinyint
declare @metatype_dummyupdate tinyint

declare @METADATA_TYPE_Missing tinyint
declare @METADATA_TYPE_Tombstone tinyint
declare @METADATA_TYPE_Contents tinyint
declare @METADATA_TYPE_ContentsDeferred tinyint
declare @METADATA_TYPE_InsertLightweight tinyint
declare @METADATA_TYPE_UpdateLightweight tinyint
declare @METADATA_TYPE_DeleteLightweight tinyint
declare @METADATA_TYPE_UpsertLightweightProcessed tinyint
declare @METADATA_TYPE_DeleteLightweightProcessed tinyint

declare @METADATA_ACTION_Default tinyint

select @metatype= changetype from dbo.MSmerge_rowtrack
where tablenick=@tablenick and rowguid=@rowguid

set @METADATA_TYPE_Missing= 0
set @METADATA_TYPE_Tombstone= 1
set @METADATA_TYPE_Contents= 2
set @METADATA_TYPE_ContentsDeferred= 3
set @METADATA_TYPE_InsertLightweight= 7
set @METADATA_TYPE_UpdateLightweight= 8
set @METADATA_TYPE_DeleteLightweight= 10
set @METADATA_TYPE_UpsertLightweightProcessed= 11
set @METADATA_TYPE_DeleteLightweightProcessed= 12

set @METADATA_ACTION_Default= 0

if @metatype=@METADATA_TYPE_DeleteLightweight
begin
-- If the metatype expresses an unprocessed delete, just make sure the delete
-- does not look as if it is already being uploaded.

update dbo.MSmerge_rowtrack
set sync_cookie= null
where tablenick=@tablenick and rowguid=@rowguid

return 0
end
else if @metatype in (@METADATA_TYPE_InsertLightweight,
@METADATA_TYPE_UpdateLightweight,
@METADATA_TYPE_UpsertLightweightProcessed)
begin
-- The metatype expresses a processed or unprocessed change; make sure the entire
-- row will be resent.
set @metatype_dummyupdate= @METADATA_TYPE_Contents
end
else if @metatype=@METADATA_TYPE_DeleteLightweightProcessed
begin
-- Make sure the delete will be resent.
set @metatype_dummyupdate= @METADATA_TYPE_Tombstone
end
else
begin
-- Figure out whether the base data exists.
declare @baserowexists bit
declare @procname nvarchar(512)
declare @postfix nchar(32)

-- Take any multipurpose proc that exists for this article, regardless of the publication.
select top 1 @postfix= procname_postfix
from dbo.sysmergearticles
where nickname = @tablenick

set @procname= quotename('MSmerge_lws_sp_multi_' + @postfix)

exec @retcode= @procname
@action= 5,
@rowguid= @rowguid,
@baserowexists= @baserowexists output
if @@error <>0 or @retcode <> 0 return (1)

if 1=@baserowexists
begin
-- The base row is here, but no metadata; make sure the entire
-- row will be resent.
set @metatype_dummyupdate= @METADATA_TYPE_ContentsDeferred
end
else
begin
-- There is neither data nor metadata. Make sure the row will be deleted.
set @metatype_dummyupdate= @METADATA_TYPE_Missing
end
end

exec @retcode= sys.sp_MSdummyupdatelightweight
@tablenick= @tablenick,
@rowguid= @rowguid,
@action= @METADATA_ACTION_Default,
@metatype= @metatype_dummyupdate,
@rowvector= null
if @@error<>0 or @retcode<>0 return 1

return 0
end

No comments:

Post a Comment

Total Pageviews