Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadjustgenerations

 

The procedure is used in making adjustments to merged replication. This is a procedure in the mssqlsystemresource database.




Syntax
create procedure sys.sp_MSadjustgenerations
    @gen bigint,
    @changecount int,
    @artnick int,
    @dbname nvarchar(258),
    @nickbin varbinary(255)
as
    declare @mismatched_artnick int
    declare @target_gen bigint
    declare @cmd nvarchar(4000)
    declare @bi_view_objid int
    declare @tombstone_rows_moved int
    declare @contents_rows_moved int
    declare @changes_in_gen int
    declare @changes_in_target_gen int
    declare @retcode smallint
    declare @procfailed bit
    declare @error int

    select @procfailed = 1
    select @retcode = 0
    select @changes_in_gen = @changecount

    begin tran
    save tran sp_MSadjustgenerations

    -- select mismatched articles from CONTENTS & TOMBSTONE
    -- note : UNION operator will eliminate duplicate results
    DECLARE #get_mismatched_artnicks CURSOR LOCAL FAST_FORWARD FOR
        select tablenick from dbo.MSmerge_contents where generation = @gen and tablenick <> @artnick
        UNION
        select tablenick from dbo.MSmerge_tombstone where generation = @gen and tablenick <> @artnick
    FOR READ ONLY

    open #get_mismatched_artnicks
    fetch #get_mismatched_artnicks into @mismatched_artnick
    while ( @@fetch_status <> -1 )
    begin

        -- get a generation to move the mismatched records, one with genstatus = 3

        -- note: since we are in the middle of coalescing loop and as it is possible that generations
        -- for @mismatched_artnick have already been coalesced,
        -- we move the records to a generation with least number of changes
        select top 1 @target_gen = generation,  @changes_in_target_gen = changecount from dbo.MSmerge_genhistory
            where art_nick = @mismatched_artnick and genstatus = 3
            order by changecount asc

        if @target_gen is null
        begin
            -- insert a new generation for @mismatched_artnick with genstatus=3
            insert into dbo.MSmerge_genhistory with (rowlock)
            (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
              values  (newid(), 3, @mismatched_artnick, @nickbin, getdate(), 0)

            select @error = @@error, @target_gen = @@identity
            if @error<>0 or @target_gen is NULL
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            select @changes_in_target_gen=0
        end

        -- update gen for mismatched_artnick

        -- update TOMBSTONE
        update dbo.MSmerge_tombstone with (rowlock) set generation = @target_gen
            where generation = @gen and tablenick = @mismatched_artnick
        select @error = @@error, @tombstone_rows_moved = @@rowcount
        if @error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        select @changes_in_target_gen = @changes_in_target_gen + @tombstone_rows_moved
        select @changes_in_gen = @changes_in_gen - @tombstone_rows_moved

        -- update PPM
        update dbo.MSmerge_past_partition_mappings with (rowlock) set generation = @target_gen
            where generation = @gen and tablenick = @mismatched_artnick
        if @@error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        -- update CONTENTS
        update dbo.MSmerge_contents with (rowlock) set generation = @target_gen
            where generation = @gen and tablenick = @mismatched_artnick
        select @error = @@error, @contents_rows_moved = @@rowcount
        if @error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        select @changes_in_target_gen = @changes_in_target_gen + @contents_rows_moved
        select @changes_in_gen = @changes_in_gen - @contents_rows_moved

        -- now update all other tables include before view that are using the old generation number
        set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles where nickname = @mismatched_artnick)
        if @bi_view_objid is not null
        begin
            set @cmd= 'update ' + @dbname + '.[dbo].' + quotename(object_name(@bi_view_objid))
                        + ' set generation=@target_gen where generation=@gen'

            exec sys.sp_executesql @cmd, N'@gen bigint, @target_gen bigint', @gen=@gen, @target_gen=@target_gen
            if @@ERROR <> 0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end
        end

        -- update CONTENTS.partchangegen
         update dbo.MSmerge_contents with (rowlock) set partchangegen = @target_gen
            where partchangegen = @gen and tablenick = @mismatched_artnick
        if @error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        -- update CONTENTS.partchangegen
        update dbo.MSmerge_contents with (rowlock) set partchangegen = (-@target_gen )
            where partchangegen = (-@gen) and tablenick = @mismatched_artnick
        if @error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        -- update METADATAACTION_REQUEST
        update dbo.MSmerge_metadataaction_request with (rowlock) set generation = @target_gen
            where generation = @gen and tablenick = @mismatched_artnick
        if @error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        -- update GENHISTORY.changecount
        update dbo.MSmerge_genhistory with (rowlock) set changecount = @changes_in_gen where generation = @gen
        if @@error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

        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

        fetch next from #get_mismatched_artnicks into @mismatched_artnick
    end

    close #get_mismatched_artnicks
    deallocate #get_mismatched_artnicks

END_TRAN:
    commit transaction
    select @procfailed = 0

EXIT_RELEASE_TRAN:
    if (@procfailed = 1)
    begin
        rollback tran sp_MSadjustgenerations
        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