Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_autoident_upgrade

  No additional text.


Syntax
create procedure sys.sp_MSmerge_autoident_upgrade
as
    declare @retcode int
    declare @pubid uniqueidentifier
    declare @artid uniqueidentifier
    declare @tablename sysname
    declare @publisher sysname
    declare @publisher_db sysname
    declare @distributor sysname
    declare @distribdb sysname
    declare @proc sysname
    declare @objid int

    declare @pub_range bigint
    declare @pub_range_orig bigint
    declare @sub_range bigint
    declare @next_seed bigint
    declare @current_max bigint
    declare @max_identity bigint
    declare @threshold int
    declare @max_used numeric(38,0)
    declare @subid uniqueidentifier
    declare @root_pubid uniqueidentifier
    declare @is_republisher bit

    -- the design for auto identity range has changed in yukon.
    -- we will upgrade identity range management to use the new
    -- identity range meta data
    if object_id('dbo.sysmergearticles', 'U') is NULL
        return 0

    if not exists (select 1 from dbo.sysmergearticles where identity_support=1)
        return 0

    -- we need to connect to the distributor to get the ranges and the next_seed
    -- we will assign new ranges to publisher and subscriber on upgrade
    declare #identity_articles cursor LOCAL FORWARD_ONLY for
        select pub.pubid, pub.publisher, pub.publisher_db, art.objid, art.artid
        from dbo.sysmergepublications pub
            inner join dbo.sysmergearticles art on pub.pubid = art.pubid
        where pub.publisher = publishingservername() and
              pub.publisher_db = db_name() and
              art.identity_support = 1
    open #identity_articles
    fetch #identity_articles into @pubid, @publisher, @publisher_db, @objid, @artid
    while (@@fetch_status<>-1)
    begin
        select @pub_range   = 0
        select @sub_range   = 0
        select @threshold   = 0
        select @next_seed   = 0
        select @current_max = 0
        select @max_identity = 0

        -- get table name from objid
        select @tablename = object_name(@objid)

        -- don't do anything if we already have entries in MSmerge_identity_range table for the given article
        if exists (select 1 from dbo.MSmerge_identity_range
                        where artid = @artid and is_pub_range = 0 and (sys.fn_MSmerge_islocalsubid(subid)=1)) and
           exists (select 1 from dbo.MSmerge_identity_range
                        where artid = @artid and is_pub_range = 1 and (sys.fn_MSmerge_islocalsubid(subid)=1)) and
           exists (select 1 from dbo.sysmergearticles
                        where pubid=@pubid and artid=@artid and pub_range is not NULL and range is not NULL)
            goto NextArticle

        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo
             @rpcsrvname = @distributor OUTPUT,
             @distribdb = @distribdb   OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            goto FAILURE

        IF @distribdb is null
        BEGIN
            RAISERROR (14071, 16, -1)
            goto FAILURE
        END

        SELECT @proc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MScheck_pub_identity'
        exec @retcode = @proc @publisher=@publisher,
                            @publisher_db=@publisher_db,
                            @tablename=@tablename,
                            @current_max=@current_max OUTPUT,
                            @pub_range=@pub_range OUTPUT,
                            @range = @sub_range OUTPUT,
                            @threshold=@threshold OUTPUT,
                            @next_seed = @next_seed OUTPUT,
                            @max_identity = @max_identity OUTPUT
        if @retcode<>0 or @@ERROR<>0
            goto FAILURE

        -- since in Yukon we use pub_range for allocating republisher range and sub_range for allocations to
        -- all subscriber as well as publisher's local allocation, if in shiloh the user set it up as pub_range
        -- less than sub_range and has republisher they would have a failure. Hence on upgrade in yukon we will set
        -- pub_range to be atleast as big as the sub range. see bug 293602
        if @pub_range < @sub_range
        begin
            select @pub_range_orig = @pub_range
            select @pub_range = @sub_range
        end
        else
            select @pub_range_orig = @pub_range

        begin tran
        save tran art_ident_update

        update dbo.sysmergearticles
            set threshold = @threshold,
                pub_range = @pub_range,
                range = @sub_range
            where artid = @artid
        if @@ERROR<>0
            goto FAILURE_CLOSE_TRAN

        -- to find if this is a republisher find the root pubid.
        select @root_pubid = pubid from dbo.sysmergearticles
            where artid = @artid and pubid <> @pubid and (sys.fn_MSmerge_islocalpubid(pubid)=0)
        if @root_pubid is not NULL
        begin
            -- this is a republisher. find the subid of the subscription to the root publication
            -- instead of the pubid of the republished publication
            select @subid = subid from dbo.sysmergesubscriptions
                where pubid=@root_pubid and (sys.fn_MSmerge_islocalsubid(subid)=1)
            -- the following should never happen
            if @subid is NULL
                goto FAILURE_CLOSE_TRAN
        end
        else
        begin
            select @subid = @pubid
        end

        -- setup the initial publisher range in MSmerge_identity_range
        -- the following may already exist if this is a republisher and it merged with its publisher first
        -- before running the snapshot of its republished publication.
        if not exists (select 1 from dbo.MSmerge_identity_range
                        where artid = @artid and is_pub_range = 1 and (sys.fn_MSmerge_islocalsubid(subid)=1))
        begin
            insert dbo.MSmerge_identity_range(subid, artid, range_begin, range_end, is_pub_range, max_used)
                values(@subid, @artid, @current_max, @max_identity, 1, @next_seed)
            if @@ERROR<>0
                goto FAILURE_CLOSE_TRAN
        end

        -- setup the subscriber range. Record the ranges that was already allocated for the publisher's local
        -- use. Further ranges will be allocated when the snapshot runs or the merge with the root publisher runs
        if not exists (select 1 from dbo.MSmerge_identity_range
                        where artid = @artid and is_pub_range = 0 and (sys.fn_MSmerge_islocalsubid(subid)=1))
        begin
            -- if this is the root publisher, in shiloh we always allocated two ranges for the publisher's local use
            -- hence get both the ranges and set them in the new tracking table
            -- if this is a republisher though we will have just one range to get from shiloh
            if @root_pubid is not NULL
            begin
                insert dbo.MSmerge_identity_range(subid, artid, range_begin, range_end, is_pub_range, max_used)
                    values(@subid, @artid, @current_max-@pub_range_orig, @current_max, 0, NULL)
            end
            else
            begin
                insert dbo.MSmerge_identity_range(subid, artid, range_begin, range_end, next_range_begin, next_range_end, is_pub_range, max_used)
                    values(@subid, @artid, (@current_max-@pub_range_orig)-@pub_range_orig, @current_max-@pub_range_orig, @current_max-@pub_range_orig, @current_max, 0, NULL)
            end
            if @@ERROR<>0
                goto FAILURE_CLOSE_TRAN
        end

        -- cleanup the shiloh entry for the identity range for this article
        if object_id('dbo.MSrepl_identity_range') is not NULL
            delete from dbo.MSrepl_identity_range where objid=@objid

        commit tran

NextArticle:

        fetch next from #identity_articles into @pubid, @publisher, @publisher_db, @objid, @artid
    end
    close #identity_articles
    deallocate #identity_articles
    return 0

FAILURE_CLOSE_TRAN:
    rollback tran art_ident_update

FAILURE:
    close #identity_articles
    deallocate #identity_articles
    return 1

 
Last revision 2008RTM
See also

  sp_helpmergepublication (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