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_MSenumgenerations90(bigint @genstart, uniqueidentifier @pubid
, int @partition_id
, int @numgens
, bigint @mingen_to_enumerate)
MetaData:
CREATE PROCEDURE sys.sp_MSenumgenerations90 (@genstart bigint = 0, @pubid uniqueidentifier = '00000000-0000-0000-0000-000000000000', @partition_id int = 0, @numgens int = 100, @mingen_to_enumerate bigint = 0, @maxgen_to_enumerate bigint = 0x7FFFFFFFFFFFFFFF output) as declare @retcode smallint ,@publication_number smallint ,@min_open_gen bigint ,@min_open_gen_guid uniqueidentifier ,@min_open_gen_art_nick int ,@min_open_gen_status tinyint ,@next_possible_watermark bigint ,@next_possible_watermark_guidsrc uniqueidentifier ,@next_possible_watermark_art_nick int ,@next_possible_watermark_pubid uniqueidentifier ,@next_possible_watermark_genstatus tinyint ,@next_possible_watermark_nicknames varbinary(1001) ,@next_possible_watermark_changecount int -- -- Check to see if current publication has permission -- if ({ fn ISPALUSER(@pubid) } <> 1) begin RAISERROR (14126, 11, -1) return (1) end if (@genstart is null) begin RAISERROR(14043, 16, -1, '@genstart', 'sp_MSenumgenerations90') return (1) end if (@pubid is null) begin RAISERROR(14043, 16, -1, '@pubid', 'sp_MSenumgenerations90') return (1) end select @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid if (@publication_number is null) begin RAISERROR(14043, 16, -1, '@pubid', 'sp_MSenumgenerations90') return (1) end -- the first time @maxgen_to_enumerate will be MAX bigint if @maxgen_to_enumerate = 0x7FFFFFFFFFFFFFFF select @maxgen_to_enumerate = ident_current('dbo.MSmerge_genhistory') if @partition_id = 0 begin select top 1 @min_open_gen = generation, @min_open_gen_guid = guidsrc, @min_open_gen_art_nick = art_nick from dbo.MSmerge_genhistory with (rowlock) where generation >= @genstart and generation <= @maxgen_to_enumerate and genstatus in (0,4) order by generation asc select top 1 @next_possible_watermark = generation, @next_possible_watermark_guidsrc = guidsrc, @next_possible_watermark_art_nick = art_nick, @next_possible_watermark_genstatus = genstatus, @next_possible_watermark_pubid = pubid, @next_possible_watermark_nicknames = nicknames, @next_possible_watermark_changecount = changecount from dbo.MSmerge_genhistory with (rowlock) where generation >= @genstart and generation < @min_open_gen and (-- for sqlce subscribers, since artnick=0, we need to enumerate generations for all publications that republish this article, hotfix 50001550 (art_nick = 0 and (genstatus = 1 or pubid in (select pubid from sysmergearticles where artid in (select artid from sysmergearticles where pubid=@pubid)))) or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = @pubid)) and genstatus in (1,2) order by generation desc select top (@numgens) * from ( select generation, guidsrc, art_nick, case when genstatus = 4 then 0 else genstatus end as genstatus, pubid, nicknames, okaytoskip = case when art_nick is not null and art_nick <> 0 and genstatus in (0,4) -- Skip all rows that are for incomplete generations for articles that have no joins. and not exists (select 1 from dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname = art_nick) and (filter_type & 1) = 1) then 1 else 0 end , changecount from (select generation, guidsrc, art_nick, genstatus, pubid, nicknames, changecount from dbo.MSmerge_genhistory with (rowlock, repeatableread) where generation >= @genstart and generation <= @maxgen_to_enumerate and generation > @mingen_to_enumerate and ( (art_nick = 0 and (genstatus = 1 or pubid in (select pubid from sysmergearticles where artid in (select artid from sysmergearticles where pubid=@pubid)))) or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = @pubid)) ) as generation_range UNION ALL -- use UNION ALL instead of UNION for perf reasons. Merge agent code will skip dupes. Will only have max 2 dupes. select generation = @next_possible_watermark, guidsrc = @next_possible_watermark_guidsrc, art_nick = @next_possible_watermark_art_nick, genstatus = @next_possible_watermark_genstatus, pubid = @next_possible_watermark_pubid, nicknames = @next_possible_watermark_nicknames, okaytoskip = 0, changecount = @next_possible_watermark_changecount where @next_possible_watermark is not null union all select generation = @min_open_gen, guidsrc= @min_open_gen_guid, art_nick=@min_open_gen_art_nick, genstatus=0, pubid=@pubid, nicknames=NULL, okaytoskip = 0, changecount=0 where @min_open_gen is not null ) as genertions order by generation ASC end else begin select top 1 @min_open_gen = generation, @min_open_gen_guid = guidsrc, @min_open_gen_art_nick = art_nick, @min_open_gen_status = case when genstatus = 4 then 0 else genstatus end from dbo.MSmerge_genhistory with (rowlock) where generation >= @genstart and generation <= @maxgen_to_enumerate and genstatus in (0,4) order by generation asc -- the following can happen right after the publication is created and the first snapshot run. We will just have -- one closed generation and no open generations in the database if there are no changes. In shiloh we used -- to always have atleast one open gen. Since in yukon that is not the case and we may not have any open generations -- if no dml has ever happened, we will just make the min generation (generation 1 should be inserted by the first makegeneration -- that is run after the snapshot as the min open gen if @min_open_gen is NULL begin select top 1 @min_open_gen = generation, @min_open_gen_guid = guidsrc, @min_open_gen_art_nick = art_nick, @min_open_gen_status = genstatus from dbo.MSmerge_genhistory with (rowlock) where generation >= @genstart and genstatus in (1,2) and generation > @mingen_to_enumerate and ((art_nick = 0 and (genstatus = 1 or pubid in (select pubid from sysmergearticles where artid in (select artid from sysmergearticles where pubid=@pubid)))) or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = @pubid)) order by generation asc select @next_possible_watermark = NULL end -- don't enumerate next possible watermark for parition groups since that generation could belong to another partition. -- for details see bug 359661 -- else begin select top 1 @next_possible_watermark = generation, @next_possible_watermark_guidsrc = guidsrc, @next_possible_watermark_art_nick = art_nick, @next_possible_watermark_genstatus = genstatus, @next_possible_watermark_pubid = pubid, @next_possible_watermark_nicknames = nicknames from dbo.MSmerge_genhistory with (rowlock) where generation >= @genstart and generation < @min_open_gen and (art_nick = 0 or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = @pubid)) and genstatus in (1,2) order by generation desc end select @next_possible_watermark_changecount = isnull(sum(changecount), 0) from dbo.MSmerge_generation_partition_mappings with (rowlock) where generation = @next_possible_watermark and ( (partition_id = @partition_id and publication_number = @publication_number) or (partition_id = @partition_id and publication_number = 0) or (partition_id = -1 and publication_number = @publication_number) or (partition_id = -1 and publication_number = 0) ) -- select top (@numgens) * from ( select mgh.generation, mgh.guidsrc, mgh.art_nick, mgh.genstatus, mgh.pubid, mgh.nicknames, okaytoskip = 0, gpm.changecount from dbo.MSmerge_genhistory mgh with (rowlock, repeatableread) inner join ( select generation, changecount = sum(changecount) from dbo.MSmerge_generation_partition_mappings with (rowlock, repeatableread) where generation >= @genstart and generation > @mingen_to_enumerate and generation <= @maxgen_to_enumerate and ( (partition_id = @partition_id and publication_number = @publication_number) or (partition_id = @partition_id and publication_number = 0) or (partition_id = -1 and publication_number = @publication_number) or (partition_id = -1 and publication_number = 0) ) group by generation ) as gpm on mgh.generation >= @genstart and mgh.generation > @mingen_to_enumerate and mgh.generation <= @maxgen_to_enumerate and mgh.art_nick in ( select sma.nickname from dbo.sysmergearticles sma with (nolock) where pubid = @pubid UNION ALL select 0 where mgh.genstatus = 1 or mgh.pubid in (select pubid from sysmergearticles where artid in (select artid from sysmergearticles where pubid=@pubid)) ) and mgh.generation = gpm.generation and mgh.genstatus in (1,2) UNION ALL -- use UNION ALL instead of UNION for perf reasons. Merge agent code will skip dupes. Will only have max 2 dupes. -- don't enumerate next possible watermark for partition groups select generation = @next_possible_watermark, guidsrc = @next_possible_watermark_guidsrc, art_nick = @next_possible_watermark_art_nick, genstatus = @next_possible_watermark_genstatus, pubid = @next_possible_watermark_pubid, nicknames = @next_possible_watermark_nicknames, okaytoskip = 0, changecount = @next_possible_watermark_changecount where @next_possible_watermark is not null UNION ALL -- use UNION ALL instead of UNION for perf reasons. Merge agent code will skip dupes. Will only have max 2 dupes. -- select generation = @min_open_gen, guidsrc= @min_open_gen_guid, art_nick=@min_open_gen_art_nick, genstatus=@min_open_gen_status, pubid=@pubid, nicknames=NULL, okaytoskip = 0, changecount=0 where @min_open_gen is not null )as generations order by generation, genstatus ASC end return (0)
No comments:
Post a Comment