create procedure sys.sp_MSmovegenzerochanges
as
    declare @min_changes_per_gen int
    declare @max_changes_per_gen int
    declare @median_changes_per_gen int
    declare @target_gen bigint
    declare @changes_in_target_gen int
    declare @error int
    declare @art_nick int
    declare @processing_order int
    declare @prev_processing_order int
    declare @prev_art_nick int
    declare @gen_change_threshold int
    declare @procfailed bit
    declare @replnick binary(6)
    declare @nickbin varbinary(255)
    declare @retcode int

    select @procfailed = 1
    select @retcode = 0

    exec @retcode= sys.sp_MSgetreplnick @replnick = @replnick out
    if @retcode<>0 or @@error<>0 goto EXIT_PROC

    -- add a guard byte
    set @nickbin= @replnick + 0xFF

    -- move generation 0 changes to a valid open generation. This code should not usually update many rows.
    -- however this needs to be done before makegeneration
    begin tran
    save tran sp_MSmovegenzerochanges

    select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
    order by processing_order, nickname
    while @art_nick is not null
    begin
        -- get the generation change threshold
        select @gen_change_threshold = min(isnull(generation_leveling_threshold,0)) from dbo.sysmergepublications
            where pubid in (select pubid from dbo.sysmergearticles where nickname = @art_nick)
        --if @gen_change_threshold = 0
        --    select @gen_change_threshold = 1000

        if (@gen_change_threshold > 0)
        begin
            select @min_changes_per_gen = @gen_change_threshold/10
            if (@min_changes_per_gen = 0)
                select @min_changes_per_gen = 1
            select @max_changes_per_gen = @gen_change_threshold * 2
            select @median_changes_per_gen = @gen_change_threshold
        end
        else
        begin
            select @min_changes_per_gen = NULL
            select @max_changes_per_gen = NULL
            select @median_changes_per_gen = NULL
        end

        -- check if there are any rows in any metadata tables with generation 0. We want to do this check first because
        -- we don't want to create an empty generation to put the generation 0 changes in when there are no changes in gen 0
        if exists (select 1 from dbo.MSmerge_tombstone with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
           exists (select 1 from dbo.MSmerge_contents with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
           exists (select 1 from dbo.MSmerge_contents with (readpast, readcommitted, rowlock) where tablenick = @art_nick and partchangegen = 0) or
           exists (select 1 from dbo.MSmerge_past_partition_mappings with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
           exists (select 1 from dbo.MSmerge_metadataaction_request with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0)
        begin
            -- update all those generation 0 rows with a valid new generation
            -- there could potentially be more than threshold number of changes in generation 0. But we will not worry about
            -- inserting leveled changes into a new gen. The leveling code after this should take care of leveling this

            -- get a valid gen for the gen 0 rows in
            select @target_gen = NULL
            exec @retcode = sys.sp_MSget_open_gen @art_nick, @min_changes_per_gen, @nickbin, @target_gen output, @changes_in_target_gen output
            if @@error<>0 or @retcode<>0 or @target_gen is NULL
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            -- in these updates we will use readpast since we don't want makegeneratio to block.
            -- if this round of makegeneration did not update some gen 0 rows it is fine the next round will pick it up.
            update dbo.MSmerge_tombstone with (rowlock, readpast, readcommitted)
                set generation = @target_gen where generation = 0 and tablenick = @art_nick
            select @error = @@error, @changes_in_target_gen = @changes_in_target_gen + @@rowcount
            if @error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update dbo.MSmerge_past_partition_mappings with (rowlock, readpast, readcommitted)
                set generation = @target_gen where generation = 0 and tablenick = @art_nick
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update dbo.MSmerge_contents with (rowlock, readpast, readcommitted)
                set generation = @target_gen where generation = 0 and tablenick = @art_nick
            select @error = @@error, @changes_in_target_gen = @changes_in_target_gen + @@rowcount
            if @error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update dbo.MSmerge_contents with (rowlock, readpast, readcommitted)
                set partchangegen = @target_gen where partchangegen = 0 and tablenick = @art_nick
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update dbo.MSmerge_metadataaction_request with (rowlock, readpast, readcommitted)
                set generation = @target_gen where generation = 0 and tablenick = @art_nick
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            -- now update the changecount of the generation to reflect the rows that have been put into it
            update dbo.MSmerge_genhistory with (rowlock)
            set changecount = @changes_in_target_gen where generation = @target_gen
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end
        end

        -- continue the artnick loop
        -- set up for next time through the loop
        select @prev_art_nick = @art_nick, @prev_processing_order = @processing_order
        select @art_nick = NULL, @processing_order = NULL

        select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
        where (processing_order = @prev_processing_order and nickname > @prev_art_nick)
        or processing_order > @prev_processing_order
        order by processing_order, nickname

    end


END_TRAN:
    commit transaction
    select @procfailed = 0

EXIT_RELEASE_TRAN:
    if (@procfailed = 1)
    begin
        rollback tran sp_MSmovegenzerochanges
        commit tran
        return 1
    end

EXIT_PROC:
    if (@procfailed = 1)
        return (1)
    else
        return (0)