May 11, 2012

sp_MSenumarticleslightweight (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_MSenumarticleslightweight(uniqueidentifier @pubid)

MetaData:

 create procedure sys.sp_MSenumarticleslightweight  
@pubid uniqueidentifier
as
declare @retcode int

exec @retcode= sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)

select
a.name, a.destination_object, a.artid, a.pubid, a.nickname,
a.column_tracking, a.destination_owner, a.identity_support, a.well_partitioned_lightweight,
a.processing_order, a.upload_options, i.range, i.threshold, a.delete_tracking, a.compensate_for_errors,
a.stream_blob_columns
from dbo.sysmergearticles a left outer join dbo.MSmerge_idrange i on a.objid = i.objid
where a.pubid=@pubid and
a.lightweight=1

sp_MSenumdeletesmetadata (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_MSenumdeletesmetadata(uniqueidentifier @pubid
, int @maxrows
, varchar @genlist
, int @tablenick
, uniqueidentifier @rowguid
, int @filter_partialdeletes
, int @specified_article_only
, bigint @mingen
, bigint @maxgen
, int @compatlevel
, bit @enumentirerowmetadata)

MetaData:

   
create procedure sys.sp_MSenumdeletesmetadata(
@pubid uniqueidentifier,
@maxrows int,
@genlist varchar(8000),
@tablenick int,
@rowguid uniqueidentifier,
@filter_partialdeletes int = 0,
@specified_article_only int = 0,
@mingen bigint = 0,
@maxgen bigint = 0,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@enumentirerowmetadata bit= 1)

as
declare @tnstring nvarchar(12)
declare @pubidstr nvarchar(38)
declare @tablenick_qual nvarchar(100)
declare @generation_clause1 nvarchar(max)
declare @generation_clause2 nvarchar(max)
declare @generation_clause3 nvarchar(max)
declare @last_art_processing_order int
declare @last_art_processing_order_str varchar(12)
declare @rowguid_clause nvarchar(100)

if (@genlist is null)
begin
RAISERROR(14043, 16, -1, '@genlist', 'sp_MSenumdeletesmetadata')
return (1)
end
if object_id('MSmerge_contents') is NULL
begin
RAISERROR(20054 , 16, -1)
return (1)
end

-- security check
if ({ fn ISPALUSER(@pubid) } <> 1)
begin
if (@pubid is NULL)
begin
RAISERROR (21723, 16, -1, 'sp_MSenumdeletesmetadata')
return 1
end
else
begin
RAISERROR (14126, 11, -1)
return 1
end
end

select @generation_clause1 = ' '
select @generation_clause2 = ' '
select @generation_clause3 = ' '



set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

-- If the filter_partialdeletes is NOT set, include all types of tombstones, else filter the type = 5 ones --
if (@filter_partialdeletes = 0)
set @tablenick_qual = ' and ts.tablenick = sm.nickname '
else
set @tablenick_qual = ' and ts.tablenick = sm.nickname and ts.type <> 5'

declare @selecttop nvarchar(50)
if (@maxrows = 0)
set @selecttop= 'select'
else
set @selecttop= 'select top ' + cast(@maxrows as nvarchar(9))

declare @mingenstr nvarchar(21)
declare @maxgenstr nvarchar(21)

select @mingenstr = convert(nvarchar, @mingen)
select @maxgenstr = convert(nvarchar, @maxgen)

declare @metadatacol nvarchar(200)

if 0 = @enumentirerowmetadata
begin
if 0 = @tablenick or 0 = @specified_article_only
set @metadatacol= 'sys.fn_MSgeneration_downloadonly(generation, tablenick), sys.fn_MSvector_downloadonly(lineage, tablenick)'
else
set @metadatacol= 'null, null'
end
else if @compatlevel >= 90
set @metadatacol= 'generation, lineage'
else
set @metadatacol= 'generation, {fn LINEAGE_90_TO_80(lineage)}'

if (@maxgen = 0)
begin
select @generation_clause1 = ' generation in ('
select @generation_clause2 = rtrim(ltrim(@genlist))
select @generation_clause3 = ') '
end
else if @mingen = @maxgen
select @generation_clause1 = ' generation = ' + @mingenstr + ' '
else
begin
select @generation_clause1 = ' generation >= ' + @mingenstr + ' and generation <= ' + @maxgenstr + ' and
generation in ('

select @generation_clause2 = rtrim(ltrim(@genlist))
select @generation_clause3 = ') '
end

if (@tablenick = 0)
begin
if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts, dbo.sysmergearticles sm
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + '
and sm.pubid = '
+ @pubidstr + @tablenick_qual + '
and (ts.tablenick in
(select nickname from dbo.sysmergepartitioninfoview where pubid = '
+ @pubidstr + '
and logical_record_view is null)
or
ts.logical_record_parent_rowguid is null
)
order by processing_order desc, tablenick desc, rowguid asc'
)

IF @@ERROR <>0
begin
return (1)
end


end
end
else
begin
set @tnstring = convert(nchar, @tablenick)
if @rowguid is NULL or @rowguid = '00000000-0000-0000-0000-000000000000'
select @rowguid_clause = ' '
else
select @rowguid_clause = ' and rowguid > ''' + convert(nchar(36), @rowguid) + ''' '

if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
if (@specified_article_only = 1)
begin
-- specify tablenick in the order by for performance reasons
execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + ' and
tablenick = '
+ @tnstring + @rowguid_clause + '
order by ts.tablenick, ts.rowguid'
)

IF @@ERROR <>0
begin
return (1)
end

end
else
begin
exec sys.sp_MSget_article_processing_order @tablenick, @pubid, @last_art_processing_order output
select @last_art_processing_order_str = convert(varchar, @last_art_processing_order)

execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts, dbo.sysmergearticles sm
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + ' and
(
(tablenick = '
+ @tnstring + @rowguid_clause + ') or
(processing_order = '
+ @last_art_processing_order_str + ' and tablenick < ' + @tnstring + ') or
processing_order < '
+ @last_art_processing_order_str + '
)
and sm.pubid = '
+ @pubidstr + @tablenick_qual + '
and (ts.tablenick in
(select nickname from dbo.sysmergepartitioninfoview where pubid = '
+ @pubidstr + '
and logical_record_view is null)
or
ts.logical_record_parent_rowguid is null
)
order by processing_order desc, tablenick desc, rowguid asc'
)

IF @@ERROR <>0
begin
return (1)
end


end
end
end

return (0)

sp_MSenumcolumns (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_MSenumcolumns(uniqueidentifier @pubid
, uniqueidentifier @artid
, uniqueidentifier @maxschemaguidforarticle
, bit @show_filtering_columns)

MetaData:

 create procedure sys.sp_MSenumcolumns  
@pubid uniqueidentifier,
@artid uniqueidentifier,
@maxschemaguidforarticle uniqueidentifier = NULL,
@show_filtering_columns bit = 0
AS
declare @retcode int
declare @procname sysname
declare @nickname int

-- security check
if ({fn ISPALUSER(@pubid)} <> 1)
begin
if (@pubid is NULL)
begin
RAISERROR (21723, 16, -1, 'sp_MSenumcolumns')
return 1
end
else
begin
RAISERROR (14126, 11, -1)
return 1
end
end

--
-- For security: break ownership chain as
-- we have no control over the proc name is
--
declare @spretcode int
,@spcall nvarchar(256)
select @procname = 'dbo.' + select_proc from dbo.sysmergearticles where pubid = @pubid and artid = @artid
select @spcall = N'exec @p1 = ' + @procname + N' @maxschemaguidforarticle = '

if @maxschemaguidforarticle is NULL
select @spcall = @spcall + 'NULL'
else
select @spcall = @spcall + '''' + convert(nvarchar(40),@maxschemaguidforarticle) + ''''

select @spcall = @spcall + ', @type = 6 '

exec @retcode = sys.sp_executesql @stmt = @spcall
,@params = N'@p1 int output'
,@p1 = @spretcode output
IF @@error<>0 or @retcode<>0 or @spretcode != 0
return 1

-- SQL2000 and lower agents pass @show_filtering_columns as 0.
if @show_filtering_columns = 1
begin
select @nickname = nickname from dbo.sysmergearticles
where pubid = @pubid
and artid = @artid

exec @retcode = sys.sp_MSgetfilteringcolumns @pubid = @pubid, @nickname = @nickname
if @@error <> 0 or @retcode <> 0
return 1
end

return 0

sp_MSenumdeleteslightweight (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_MSenumdeleteslightweight(uniqueidentifier @pubid
, int @tablenick
, uniqueidentifier @lastrowguid
, int @maxrows)

MetaData:

 create procedure sys.sp_MSenumdeleteslightweight  
@pubid uniqueidentifier,
@tablenick int,
@lastrowguid uniqueidentifier,
@maxrows int
as
set nocount on

declare @pubnick int
declare @METADATA_TYPE_DeleteLightweight tinyint
declare @last_art_processing_order int
declare @maxint int
declare @retcode int

-- security check
exec @retcode = sys.sp_MSreplcheck_subscribe
if (@retcode <> 0 or @@error <> 0)
return 1

set @METADATA_TYPE_DeleteLightweight= 10
set @maxint= 2147483647

if 0 = @maxrows
begin
set @maxrows= @maxint
end

select @pubnick = sync_info from dbo.MSmerge_replinfo where repid = @pubid

-- Step 1: Set the localid value for the deletes that will be enumerated.
-- That localid will be reset upon downloading the proxied metadata
-- from that publisher.
-- Step 2: Enumerate all those rows whose localid has just been set.
--
if @tablenick = 0
begin
-- Step 1
update rt
set rt.sync_cookie= @pubnick
from dbo.MSmerge_rowtrack rt join dbo.sysmergearticles art
on rt.tablenick = art.nickname
where art.pubid = @pubid and
rt.changetype = @METADATA_TYPE_DeleteLightweight

-- Step 2
select top (@maxrows) rt.tablenick, rt.rowguid, rt.rowvector
from dbo.MSmerge_rowtrack rt join dbo.sysmergearticles art
on rt.tablenick = art.nickname
where art.pubid = @pubid and
rt.changetype = @METADATA_TYPE_DeleteLightweight and
rt.sync_cookie = @pubnick
order by art.processing_order desc, rt.tablenick desc, rt.rowguid asc
end
else
begin
exec sys.sp_MSget_lightweightarticle_processing_order @tablenick, @pubid, @last_art_processing_order output

-- Step 1
update rt
set rt.sync_cookie= @pubnick
from dbo.MSmerge_rowtrack rt join dbo.sysmergearticles art
on rt.tablenick = art.nickname
where art.pubid = @pubid and
rt.changetype = @METADATA_TYPE_DeleteLightweight and
((rt.tablenick = @tablenick and rt.rowguid > @lastrowguid)
or
(art.processing_order = @last_art_processing_order and rt.tablenick < @tablenick)
or
art.processing_order < @last_art_processing_order
)

-- Step 2
select top (@maxrows) rt.tablenick, rt.rowguid, rt.rowvector
from dbo.MSmerge_rowtrack rt join dbo.sysmergearticles art
on rt.tablenick = art.nickname
where art.pubid = @pubid and
rt.changetype = @METADATA_TYPE_DeleteLightweight and
((rt.tablenick = @tablenick and rt.rowguid > @lastrowguid)
or
(art.processing_order = @last_art_processing_order and rt.tablenick < @tablenick)
or
art.processing_order < @last_art_processing_order
) and
rt.sync_cookie = @pubnick
order by art.processing_order desc, rt.tablenick desc, rt.rowguid asc
end

return 0

sp_MSenumdeletes_forpartition (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_MSenumdeletes_forpartition(int @partition_id
, int @maxrows
, varchar @genlist
, int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid
, bigint @mingen
, bigint @maxgen
, bit @enumentirerowmetadata)

MetaData:

 create procedure sys.sp_MSenumdeletes_forpartition  
(
@partition_id int = 0,
@maxrows int = 0,
@genlist varchar(8000) = NULL,
@tablenick int = 0,
@rowguid uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@pubid uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@mingen bigint = 0,
@maxgen bigint = 0,
@enumentirerowmetadata bit= 1
)
as
declare @generation_clause nvarchar(max)
declare @generation_declare_list nvarchar(max)
declare @generation_select_list nvarchar(max)
declare @generation_union_list nvarchar(max)
declare @genlist_innerjoin_clause nvarchar(max)
declare @ts_gen_clause nvarchar(max)
declare @command nvarchar(max)
declare @selecttop nvarchar(50)
declare @last_art_processing_order int
declare @metadatacols nvarchar(200)
declare @retcode int
declare @dbname nvarchar(258)
declare @allow_partition_realignment bit
declare @ts_rowguid_clause nvarchar(100)
declare @pcpm_rowguid_clause nvarchar(100)
declare @logical_record_clause nvarchar(400)
declare @publication_number smallint
declare @orderby_clause nvarchar(200)


-- Security Checking
-- PAL user has access
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick, @partition_id = @partition_id
if (@retcode <> 0) or (@@error <> 0)
return 1

exec sys.sp_MSmerge_parsegenlist @genlist, @generation_declare_list output, @generation_select_list output, @generation_union_list output

select @dbname = quotename(db_name())

select @generation_clause = N' '
select @ts_gen_clause = N' '
select @genlist_innerjoin_clause = N' '

select @command = @generation_declare_list + @generation_select_list

if (@maxrows = 0)
begin
set @selecttop= N'
select distinct '

set @orderby_clause = N' order by sma.processing_order desc, ts.tablenick desc'
end
else
begin
set @selecttop= N'
select distinct top '
+ cast(@maxrows as nvarchar(9))
set @orderby_clause = N' order by sma.processing_order desc, ts.tablenick desc, ts.rowguid asc'
end

if 1 = @enumentirerowmetadata
set @metadatacols= N'pcpm.generation, ts.lineage'
else
set @metadatacols= N'sys.fn_MSgeneration_downloadonly(pcpm.generation, ts.tablenick), sys.fn_MSvector_downloadonly(ts.lineage, ts.tablenick)'

if @rowguid is NULL or @rowguid = '00000000-0000-0000-0000-000000000000'
begin
select @ts_rowguid_clause = N' '
select @pcpm_rowguid_clause = N' '
end
else
begin
select @pcpm_rowguid_clause = N' and pcpm.rowguid > @rowguid '
select @ts_rowguid_clause = N' and ts.rowguid > @rowguid '
end

select top 1 @publication_number = publication_number,
@allow_partition_realignment=allow_partition_realignment from dbo.sysmergepublications where pubid = @pubid

if exists (select 1 from dbo.sysmergepartitioninfoview where logical_record_view is not NULL)
select @logical_record_clause = N'
and (ts.tablenick in
(select nickname from dbo.sysmergepartitioninfoview where pubid = @pubid
and logical_record_view is null)
or
ts.logical_record_parent_rowguid is null
)'

else
select @logical_record_clause = N''

if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
if (@maxgen = 0)
begin
select @ts_gen_clause = N' '
select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on pcpm.generation = genlist.gen
and genlist.gen is not NULL'

end
else if @mingen = @maxgen
begin
select @generation_clause = N' and pcpm.generation = @mingen '
select @ts_gen_clause = N' and ts.generation = @mingen '
end
else
begin
select @generation_clause = N' and pcpm.generation >= @mingen and pcpm.generation <= @maxgen '
select @ts_gen_clause = N' and ts.generation >= @mingen and ts.generation <= @maxgen '
select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on pcpm.generation = genlist.gen
and genlist.gen is not NULL'

end

if @tablenick = 0
begin
-- the processing order in the select is not needed. It is only being selected so that we can use distict
select @command = @command + @selecttop + N' ts.tablenick, ts.rowguid, pcpm.generation, ts.lineage, ts.type, sma.processing_order
from '
+ @dbname + N'.[dbo].[MSmerge_tombstone] ts
inner join '
+ @dbname + N'.[dbo].[sysmergearticles] sma
on ts.tablenick = sma.nickname '
+ @ts_rowguid_clause + N'
inner join '
+ @dbname + N'.[dbo].[MSmerge_past_partition_mappings] pcpm
on pcpm.tablenick = ts.tablenick and pcpm.rowguid = ts.rowguid '
+ @pcpm_rowguid_clause + N'
'
+ @generation_clause + @ts_gen_clause + N'
and sma.pubid = @pubid
and
(
(pcpm.partition_id = @partition_id and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = @partition_id and pcpm.publication_number = 0) or
(pcpm.partition_id = -1 and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = -1 and pcpm.publication_number = 0)
) '

+ @logical_record_clause
if (@allow_partition_realignment = 0)
begin
select @command = @command + N' and pcpm.reason = 1'
end
select @command = @command + N'
'
+ @genlist_innerjoin_clause
select @command = @command + @orderby_clause
end
else
begin
exec sys.sp_MSget_article_processing_order @tablenick, @pubid, @last_art_processing_order output

-- the processing order in the select is not needed. It is only being selected so that we can use distict
select @command = @command + @selecttop + N' ts.tablenick, ts.rowguid, pcpm.generation, ts.lineage, ts.type, sma.processing_order
from '
+ @dbname + N'.[dbo].[MSmerge_tombstone] ts
inner join '
+ @dbname + N'.[dbo].[sysmergearticles] sma on ts.tablenick = sma.nickname
inner join '
+ @dbname + N'.[dbo].[MSmerge_past_partition_mappings] pcpm
on pcpm.tablenick = ts.tablenick and pcpm.rowguid = ts.rowguid
'
+ @generation_clause + @ts_gen_clause + N'
and
(
(ts.tablenick = @tablenick'
+ @ts_rowguid_clause + N') or
(sma.processing_order = @last_art_processing_order and ts.tablenick < @tablenick) or
sma.processing_order < @last_art_processing_order
)
and sma.pubid = @pubid
and
(
(pcpm.partition_id = @partition_id and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = @partition_id and pcpm.publication_number = 0) or
(pcpm.partition_id = -1 and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = -1 and pcpm.publication_number = 0)
) '

+ @logical_record_clause
if (@allow_partition_realignment = 0)
begin
select @command = @command + N' and pcpm.reason = 1'
end
select @command = @command + N'
'
+ @genlist_innerjoin_clause
select @command = @command + @orderby_clause
end

exec sys.sp_executesql @command, N'@tablenick int = 0, @rowguid uniqueidentifier = ''00000000-0000-0000-0000-000000000000'', @pubid uniqueidentifier = ''00000000-0000-0000-0000-000000000000'', @publication_number smallint = 0, @partition_id int = 0, @mingen bigint = 0, @maxgen bigint = 0, @last_art_processing_order int = 0',
@tablenick=@tablenick, @rowguid=@rowguid, @pubid=@pubid, @publication_number=@publication_number, @partition_id=@partition_id, @mingen=@mingen, @maxgen=@maxgen, @last_art_processing_order = @last_art_processing_order
if @@error <> 0
return 1
end


return (0)

sp_MSenumcolumnslightweight (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_MSenumcolumnslightweight(uniqueidentifier @pubid
, uniqueidentifier @artid)

MetaData:

 create procedure sys.sp_MSenumcolumnslightweight  
@pubid uniqueidentifier,
@artid uniqueidentifier
AS
declare @retcode int
declare @procname sysname
declare @postfix nchar(32)

exec @retcode= sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)


select @postfix= procname_postfix
from dbo.sysmergearticles
where artid = @artid and pubid = @pubid

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

exec @retcode= @procname @action=6
if @@error<>0 or @retcode<>0 return (1)

return 0

sp_MSenumchangeslightweight (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_MSenumchangeslightweight(uniqueidentifier @pubid
, int @tablenick
, uniqueidentifier @lastrowguid
, int @maxrows)

MetaData:

 create procedure sys.sp_MSenumchangeslightweight  
@pubid uniqueidentifier,
@tablenick int,
@lastrowguid uniqueidentifier,
@maxrows int
as
set nocount on

declare @retcode int
declare @postfix nchar(32)
declare @procname sysname
declare @pubnick int
declare @METADATA_TYPE_InsertLightweight tinyint
declare @METADATA_TYPE_UpdateLightweight tinyint
declare @maxint int


-- do permission checking
exec @retcode = sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0 return (1)

set @METADATA_TYPE_InsertLightweight= 7
set @METADATA_TYPE_UpdateLightweight= 8
set @maxint= 2147483647

if 0 = @maxrows
begin
set @maxrows= @maxint
end

select @pubnick = sync_info from dbo.MSmerge_replinfo where repid = @pubid

select @postfix= procname_postfix
from dbo.sysmergearticles
where pubid = @pubid and nickname = @tablenick

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

-- Step 1: Set the sync_cookie value for the updates/inserts that will be enumerated.
-- That sync_cookie will be reset upon downloading the proxied metadata
-- from that publisher.
-- Step 2: Enumerate all those rows whose sync_cookie has just been set.
--

-- Step 1
update dbo.MSmerge_rowtrack
set sync_cookie= @pubnick
where tablenick = @tablenick and
rowguid > @lastrowguid and
changetype in (@METADATA_TYPE_InsertLightweight,
@METADATA_TYPE_UpdateLightweight)

-- Step 2
exec @retcode= @procname
@action= 2,
@pubnick= @pubnick,
@rowguid= @lastrowguid,
@maxrows= @maxrows
if @@error <> 0 or @retcode <> 0 return 1

return 0

sp_MSenumchangesdirect (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_MSenumchangesdirect(int @maxrows
, varchar @genlist
, int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid
, bigint @oldmaxgen
, bigint @mingen
, bigint @maxgen
, int @compatlevel
, bit @enumentirerowmetadata
, bit @blob_cols_at_the_end
, uniqueidentifier @maxschemaguidforarticle)

MetaData:

 create procedure sys.sp_MSenumchangesdirect  
(@maxrows int,
@genlist varchar(2000),
@tablenick int = 0,
@rowguid uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@pubid uniqueidentifier = NULL,
@oldmaxgen bigint =0,
@mingen bigint = 0,
@maxgen bigint = 0,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@enumentirerowmetadata bit= 1,
@blob_cols_at_the_end bit=0,-- when this bit is set the blob columns are returned in the end of the row set.
@maxschemaguidforarticle uniqueidentifier = NULL)
as
declare @tnstring nvarchar(12)
, @oldmaxgenstr nvarchar(21)
, @rgstring nvarchar(38)
, @generation_clause nvarchar(max)
, @generation_declare_list nvarchar(max)
, @generation_select_list nvarchar(max)
, @generation_union_list nvarchar(max)
, @genlist_innerjoin_clause nvarchar(max)
, @quoted_owner_qualified_table_name nvarchar(776) -- 128*2 for dbname, 128*2 for owner, 128*2 for tablename, 8 for [].[].[]
, @objid int
, @selecttop nvarchar(50)
, @mingenstr nvarchar(21)
, @maxgenstr nvarchar(21)
, @metadatacols nvarchar(300)
, @cCols int
, @rowguid_clause nvarchar(200)
, @cmd nvarchar(max)
, @column_list nvarchar(max)
, @column_list_blob nvarchar(max)
, @retcode smallint
, @artid uniqueidentifier
, @dbname nvarchar(258)
, @currentmaxschemaguidforarticle uniqueidentifier


-- Security Checking
-- PAL user access
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick
if (@retcode <> 0) or (@@error <> 0)
return 1

exec sys.sp_MSmerge_parsegenlist @genlist, @generation_declare_list output, @generation_select_list output, @generation_union_list output

select @generation_clause = ' '
select @genlist_innerjoin_clause = ' '

select @dbname = quotename(db_name())

set @tnstring = convert(nchar, @tablenick)
select @objid = objid, @artid = artid from dbo.sysmergearticles where nickname = @tablenick
select @quoted_owner_qualified_table_name = @dbname + '.' + quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
from sys.objects where object_id = @objid

select @cmd = @generation_declare_list + @generation_select_list

if (@maxrows = 0)
set @selecttop= '
select'

else
set @selecttop= '
select top '
+ cast(@maxrows as nvarchar(9))

select @mingenstr = convert(nvarchar, @mingen)
select @maxgenstr = convert(nvarchar, @maxgen)

if @rowguid = '00000000-0000-0000-0000-000000000000'
select @rowguid_clause = ' '
else
begin
-- set @rgstring = '''' + convert(nchar(36), @rowguid) + ''''
-- select @rowguid_clause = ' and mc.rowguid > ' + @rgstring + ' and t.rowguidcol > ' + @rgstring
select @rowguid_clause = ' and mc.rowguid > @rowguid and t.rowguidcol > @rowguid '
end

if 0 = @enumentirerowmetadata
set @metadatacols= 'mc.generation, null, null'
else if @compatlevel >= 90
set @metadatacols= 'mc.generation, mc.lineage, mc.colv1'
else if sys.fn_fIsColTracked(@tablenick) = 1
begin
set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)
set @metadatacols= 'mc.generation, {fn LINEAGE_90_TO_80(mc.lineage)}, {fn COLV_90_TO_80(mc.colv1,' + cast(@cCols as nvarchar(6)) +')}'
end
else
set @metadatacols= 'mc.generation, {fn LINEAGE_90_TO_80(mc.lineage)}, mc.colv1'

select @column_list = column_list, @column_list_blob = column_list_blob from dbo.sysmergepartitioninfoview where artid = @artid and pubid=@pubid

-- Use the column list with blob columns in the end if the blob_cols_at_the_end is 1
if @blob_cols_at_the_end = 1
select @column_list = @column_list_blob

if @column_list is NULL
begin
exec @retcode = sys.sp_MSgetviewcolumnlist @pubid = @pubid, @source_objid = @objid, @column_list = @column_list OUTPUT, @prefix_tablename = 't.', @blob_cols_at_the_end=@blob_cols_at_the_end
if @@ERROR<>0 OR @retcode <> 0
return @retcode
end

if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
if (@maxgen = 0)
begin

select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on mc.generation = genlist.gen
and genlist.gen is not NULL'

end
else if @maxgen = @mingen
select @generation_clause = ' mc.generation = ' + @mingenstr + ' '
else
begin

select @generation_clause = N' mc.generation >= ' + @mingenstr + N' and mc.generation <= ' + @maxgenstr + N' '
select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on mc.generation = genlist.gen
and genlist.gen is not NULL'

end

if @oldmaxgen > 0
begin
set @oldmaxgenstr = convert(nvarchar, @oldmaxgen)
select @cmd = @cmd + @selecttop + N' mc.tablenick, mc.rowguid, ' + @metadatacols + N', ' + @column_list + N'
from '
+ @dbname + N'.[dbo].[MSmerge_contents] mc
inner join '
+ @quoted_owner_qualified_table_name + N' t
on mc.rowguid = t.rowguidcol
'
+ @genlist_innerjoin_clause + N'
where (
('
+ @generation_clause + N')
or mc.generation = 0 or mc.generation > '
+ @oldmaxgenstr + N'
)
and mc.tablenick = '
+ @tnstring +
@rowguid_clause + N'
order by mc.tablenick, mc.rowguid'

end
else
begin
select @cmd = @cmd + @selecttop + N' mc.tablenick, mc.rowguid, ' + @metadatacols + N', ' + @column_list + N'
from '
+ @dbname + N'.[dbo].[MSmerge_contents] mc
inner join '
+ @quoted_owner_qualified_table_name + N' t
on mc.rowguid = t.rowguidcol
'
+ @genlist_innerjoin_clause + N'
where '
+ @generation_clause + N'
and mc.tablenick = '
+ @tnstring +
@rowguid_clause + N'
order by mc.tablenick, mc.rowguid'

end


select @currentmaxschemaguidforarticle = sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid)

-- At the publisher, if the max schema guid for article has changed and is different from the agent's schema version for article raise error --
if ((sys.fn_MSmerge_islocalpubid(@pubid) = 1) and (@maxschemaguidforarticle IS NOT NULL) and (@currentmaxschemaguidforarticle <> @maxschemaguidforarticle))
begin
RAISERROR (25007, 11, -1)
return 5
end

exec sys.sp_executesql @cmd, N'@rowguid uniqueidentifier = ''00000000-0000-0000-0000-000000000000''', @rowguid=@rowguid
if @@error <>0
begin
return (1)
end
end

return (0)

sp_MSenumchanges_notbelongtopartition (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_MSenumchanges_notbelongtopartition(int @partition_id
, int @maxrows
, varchar @genlist
, int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid
, bigint @mingen
, bigint @maxgen
, bit @enumentirerowmetadata)

MetaData:

 create procedure sys.sp_MSenumchanges_notbelongtopartition  
(
@partition_id int = 0,
@maxrows int = 0,
@genlist varchar(8000) = NULL,
@tablenick int = 0,
@rowguid uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@pubid uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@mingen bigint = 0,
@maxgen bigint = 0,
@enumentirerowmetadata bit= 1
)
as
declare @generation_clause nvarchar(max)
declare @generation_declare_list nvarchar(max)
declare @generation_select_list nvarchar(max)
declare @generation_union_list nvarchar(max)
declare @genlist_innerjoin_clause nvarchar(max)
declare @command nvarchar(max)
declare @selecttop nvarchar(50)
declare @last_art_processing_order int
declare @metadatacols nvarchar(200)
declare @retcode int
declare @dbname nvarchar(258)
declare @publication_number smallint
declare @allow_partition_realignment bit
declare @cpm_rowguid_clause nvarchar(100)
declare @pcpm_rowguid_clause nvarchar(100)
declare @mc_rowguid_clause nvarchar(100)
declare @orderby_clause nvarchar(200)


-- Security Checking
-- PAL user has access
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick, @partition_id = @partition_id
if (@retcode <> 0) or (@@error <> 0)
return 1

select top 1 @publication_number = publication_number, @allow_partition_realignment = allow_partition_realignment
from dbo.sysmergepublications where pubid = @pubid

exec sys.sp_MSmerge_parsegenlist @genlist, @generation_declare_list output, @generation_select_list output, @generation_union_list output

select @generation_clause = N' '
select @genlist_innerjoin_clause = N' '

select @dbname = quotename(db_name())

select @command = @generation_declare_list + @generation_select_list

if (@maxrows = 0)
begin
set @selecttop= N'
select'

set @orderby_clause = N' order by sma.processing_order desc, mc.tablenick desc'
end
else
begin
set @selecttop= N'
select top '
+ cast(@maxrows as nvarchar(9))
set @orderby_clause = N' order by sma.processing_order desc, mc.tablenick desc, mc.rowguid asc'
end

if 1 = @enumentirerowmetadata
set @metadatacols= N'pcpm.generation, mc.lineage'
else
set @metadatacols= N'sys.fn_MSgeneration_downloadonly(pcpm.generation, mc.tablenick), sys.fn_MSvector_downloadonly(mc.lineage, mc.tablenick)'

if @rowguid is NULL or @rowguid = '00000000-0000-0000-0000-000000000000'
begin
select @cpm_rowguid_clause = N' '
select @pcpm_rowguid_clause = N' '
select @mc_rowguid_clause = N' '
end
else
begin
select @cpm_rowguid_clause = N' and cpm.rowguid > @rowguid '
select @pcpm_rowguid_clause = N' and pcpm.rowguid > @rowguid '
select @mc_rowguid_clause = N' and mc.rowguid > @rowguid '
end

if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin

if (@maxgen = 0)
begin

select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on pcpm.generation = genlist.gen
and genlist.gen is not NULL'


end
else if @mingen = @maxgen
begin
select @generation_clause = N' and pcpm.generation = @mingen '
end
else
begin
select @generation_clause = N' and pcpm.generation >= @mingen and pcpm.generation <= @maxgen '
select @genlist_innerjoin_clause = N'
inner join ( '
+ @generation_union_list + '
) as genlist
on pcpm.generation = genlist.gen
and genlist.gen is not NULL'

end

if @tablenick = 0
begin
select @command = @command + @selecttop + N' mc.tablenick, mc.rowguid, pcpm.generation, mc.lineage, 5
from '
+ @dbname + N'.[dbo].[MSmerge_contents] mc
inner join '
+ @dbname + N'.[dbo].[sysmergearticles] sma
on mc.tablenick = sma.nickname and
sma.pubid = @pubid '
+ @mc_rowguid_clause + '
inner join '
+ @dbname + N'.[dbo].[MSmerge_past_partition_mappings] pcpm
on pcpm.tablenick = mc.tablenick and
pcpm.rowguid = mc.rowguid and
(
(pcpm.partition_id = @partition_id and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = @partition_id and pcpm.publication_number = 0) or
(pcpm.partition_id = -1 and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = -1 and pcpm.publication_number = 0))
'
+ @generation_clause +
@pcpm_rowguid_clause + N'
'
+ @genlist_innerjoin_clause + N'
-- use the left outer join to find what partition changes need to enumerated as deletes (yiche)
left outer join '
+ @dbname + N'.[dbo].[MSmerge_current_partition_mappings] cpm
on cpm.tablenick = pcpm.tablenick and
cpm.rowguid = pcpm.rowguid and
(
(cpm.partition_id = @partition_id and cpm.publication_number = @publication_number) or
(cpm.partition_id = @partition_id and cpm.publication_number = 0) or
(cpm.partition_id = -1 and cpm.publication_number = @publication_number) or
(cpm.partition_id = -1 and cpm.publication_number = 0)
)
where cpm.rowguid is NULL '

if (@allow_partition_realignment = 0)
begin
select @command = @command + N' and pcpm.reason = 1'
end

select @command = @command + @orderby_clause
end
else
begin
exec sys.sp_MSget_article_processing_order @tablenick, @pubid, @last_art_processing_order output

select @command = @command + @selecttop + N' mc.tablenick, mc.rowguid, pcpm.generation, mc.lineage, 5
from '
+ @dbname + N'.[dbo].[MSmerge_contents] mc
inner join '
+ @dbname + N'.[dbo].[sysmergearticles] sma
on mc.tablenick = sma.nickname and
sma.pubid = @pubid
and
(
(mc.tablenick = @tablenick '
+ @mc_rowguid_clause + N' ) or
(sma.processing_order = @last_art_processing_order and mc.tablenick < @tablenick) or
sma.processing_order < @last_art_processing_order
)
and
inner join '
+ @dbname + N'.[dbo].[MSmerge_past_partition_mappings] pcpm
on pcpm.tablenick = mc.tablenick and
pcpm.rowguid = mc.rowguid and
(
(pcpm.partition_id = @partition_id and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = @partition_id and pcpm.publication_number = 0) or
(pcpm.partition_id = -1 and pcpm.publication_number = @publication_number) or
(pcpm.partition_id = -1 and pcpm.publication_number = 0)
)
'
+ @generation_clause + N'
'
+ @genlist_innerjoin_clause + N'
-- use the left outer join to find what partition changes need to enumerated as deletes (yiche)
left outer join '
+ @dbname + N'.dbo.MSmerge_current_partition_mappings cpm
on cpm.tablenick = pcpm.tablenick and
cpm.rowguid = pcpm.rowguid and
(
(cpm.partition_id = @partition_id and cpm.publication_number = @publication_number) or
(cpm.partition_id = @partition_id and cpm.publication_number = 0) or
(cpm.partition_id = -1 and cpm.publication_number = @publication_number) or
(cpm.partition_id = -1 and cpm.publication_number = 0)
)
where cpm.rowguid is NULL '


if (@allow_partition_realignment = 0)
begin
select @command = @command + N' and pcpm.reason = 1'
end

select @command = @command + @orderby_clause
end

exec sys.sp_executesql @command, N'@tablenick int = 0, @rowguid uniqueidentifier = ''00000000-0000-0000-0000-000000000000'', @pubid uniqueidentifier = ''00000000-0000-0000-0000-000000000000'', @publication_number smallint = 0, @partition_id int = 0, @mingen bigint = 0, @maxgen bigint = 0, @last_art_processing_order int = 0',
@tablenick=@tablenick, @rowguid=@rowguid, @pubid=@pubid, @publication_number=@publication_number, @partition_id=@partition_id, @mingen=@mingen, @maxgen=@maxgen, @last_art_processing_order = @last_art_processing_order
if @@error <> 0
return 1

end


return (0)

Total Pageviews