Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmovegenzerochanges

  No additional text.


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

 
Last revision 2008RTM
See also

  sp_MSdrop_rlcore (Procedure)
sp_MSmakegeneration (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