Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenumgenerations90

  No additional text.


Syntax
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)

 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash