Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenumgenerations

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_MSenumgenerations
    (@genstart bigint,
     @pubid uniqueidentifier,
     @return_count_of_generations bit = 0)
as
    declare @generation_range TABLE (generation bigint NOT NULL, guidsrc uniqueidentifier NOT NULL, art_nick int NULL, genstatus tinyint NOT NULL, pubid uniqueidentifier NULL, nicknames varbinary(1000) NOT NULL, okaytoskip bit NOT NULL, changecount int NO
T NULL)
    declare @retcode    smallint
                        ,@rowcount   int
                        ,@maxgen                bigint

        /*
        ** Check to see if current publication has permission
        */

        if (@genstart is null)
                begin
                RAISERROR(14043, 16, -1, '@genstart', 'sp_MSenumgenerations')
                return (1)
                end
        if (@pubid is null)
                begin
                RAISERROR(14043, 16, -1, '@pubid', 'sp_MSenumgenerations')
                return (1)
                end

        if ({ fn ISPALUSER(@pubid) } <> 1)
        begin
                RAISERROR (14126, 11, -1)
                return (1)
        end

    insert into @generation_range (generation, guidsrc, art_nick, genstatus, pubid, nicknames, okaytoskip, changecount)
            select DISTINCT generation, guidsrc, art_nick, genstatus, pubid, {fn REPLNICKARRAY_90_TO_80(nicknames)}, 0, changecount
        from dbo.MSmerge_genhistory with (rowlock)
        where generation >= @genstart
        and (art_nick = 0 or art_nick is NULL or
                    art_nick in (select nickname from dbo.sysmergearticles
                    where pubid = @pubid))
    select @rowcount = @@rowcount

    if (@return_count_of_generations = 1)
        select @rowcount

    -- we want open generations inserted by merge to look like local open generations
    -- update @generation_range set genstatus = 0 where genstatus = 4

    -- optimizations
    -- 1. skip all rows that are for incomplete generations for articles that have no joins.
    -- 2. skip all rows for join articles if all the join article rows are incomplete generations.
    update @generation_range set okaytoskip = 1
    where art_nick is not null and art_nick <> 0
    and genstatus in (0, 4)
    and
    (
        (
            -- 1. skip all rows that are for incomplete generations for articles that have no joins.
            not exists (select 1 from dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname = art_nick) and (filter_type & 1) = 1)
        )
        or
        (
            -- 2. skip all rows for join articles if all the rows for join and joined articles (i.e. the articles represented by join_nickname
            --    and art_nickname in dbo.sysmergesubsetfilters) are incomplete generations.

            art_nick in (select join_nickname from dbo.sysmergesubsetfilters where (filter_type & 1) = 1)
            and not exists
            (
                    select 1 from @generation_range b where b.genstatus in (1,2)
                    and exists (select 1 from dbo.sysmergesubsetfilters where (join_nickname = b.art_nick or art_nickname = b.art_nick) and (filter_type & 1) = 1)
                    and b.generation > @genstart
            )
        )
    )

    -- Merge agent is SQL 8.0 or lower

    -- generations have to fit into 4 byte range
    declare @maxgen_80 int
    set @maxgen_80= 2147483647
    if (select max(generation) from @generation_range) > @maxgen_80
    begin
        raiserror(21521,16,1,@maxgen_80)
        return(1)
    end

    declare @arbitrary_guidlocal uniqueidentifier, @guidnull uniqueidentifier
    select @arbitrary_guidlocal = newid()
    select @guidnull = '00000000-0000-0000-0000-000000000000'

    select generation, guidsrc, art_nick,
    case when genstatus in (0, 4) then @guidnull else @arbitrary_guidlocal end,
    pubid, nicknames, okaytoskip from @generation_range
    ORDER BY generation ASC

    return (0)

 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MSenumgenerations90 (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