May 14, 2012

sp_MSenumgenerations90 (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_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

Total Pageviews