Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MSmerge_upgrade_subscriber @upgrade_metadata bit = 1, @upgrade_done bit = NULL output
    -- this stored procedure is called every time the merge runs
    -- however we will only do anything if we find that upgrade is in progress
    declare @retcode int
    declare @artid uniqueidentifier
    declare @subid uniqueidentifier
    declare @objid int
    declare @qualified_table_name nvarchar(300)
    declare @pubid uniqueidentifier
    declare @subscriber_type int
    declare @is_republisher bit
    declare @source_owner sysname
    declare @source_object sysname
    declare @procsuffix nvarchar(100)
    declare @range_begin bigint
    declare @range_end bigint

    exec @retcode = sys.sp_MSreplcheck_subscribe
    if @retcode<>0 or @@error<>0
        return 1

    exec @retcode = sys.sp_MSgetMergeUpgradeSubscriberAppLock
    if @retcode<>0 or @@error<>0
        return 1

    if object_id('MSmerge_upgrade_in_progress', 'U') is NULL
        select @upgrade_done = 0
        goto success

    -- first upgrade the metadata tables
    if @upgrade_metadata = 1
        exec @retcode = sys.sp_MSmerge_metadataupgrade
        if @retcode<>0 or @@error<>0
            goto error

    if exists (select 1 from dbo.sysmergearticles where sys.fn_MSmerge_islocalpubid(pubid)=1)
        select @is_republisher = 1
        select @is_republisher = 0

    -- we will recreate the article procs and article triggers
    -- we will only do for the subscriptions. For the publications the snapshot needs to be run.
    declare #articles cursor LOCAL FORWARD_ONLY for
        select distinct artid, objid, pubid from dbo.sysmergearticles where sys.fn_MSmerge_islocalpubid(pubid)=0
    open #articles
    fetch #articles into @artid, @objid, @pubid
    while (@@fetch_status<>-1)
        select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
        select @qualified_table_name = quotename(@source_owner) + '.' + quotename(@source_object)
        if @qualified_table_name is NULL
            goto NextArticle

        -- we really cannot be subscribing to two different publications for the same table.
        -- However there is a possibility that we could be

        -- drop the ins/upd/sel procs which have names like sp_ins_*
        exec @retcode = sys.sp_MSmerge_dropdownlevelprocs @artid, @pubid
        if @retcode<>0 or @@error<>0
            goto error

        select @procsuffix = sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)
        if @procsuffix is NULL
            goto error

        -- update the names in sysmergearticles to use ins_sp_*
        update dbo.sysmergearticles
            set insert_proc = 'MSmerge_ins_sp_' + @procsuffix,
                update_proc = 'MSmerge_upd_sp_' + @procsuffix,
                select_proc = 'MSmerge_sel_sp_' + @procsuffix,
                metadata_select_proc = 'MSmerge_sel_sp_' + @procsuffix + '_metadata',
                delete_proc = 'MSmerge_del_sp_' + @procsuffix
            where artid = @artid and pubid = @pubid
        if @@error<>0
            goto error

        exec @retcode = sys.sp_MSmakearticleprocs @pubid, @artid, 1
        if @retcode<>0 or @@error<>0
            goto error

        -- if the article uses auto identity range create the subscription entry in MSmerge_identity_range
        if exists (select 1 from dbo.sysmergearticles where artid=@artid and pubid=@pubid and identity_support=1)
            select @subid = subid, @subscriber_type = subscriber_type from dbo.sysmergesubscriptions
                where pubid = @pubid and sys.fn_MSmerge_islocalsubid(subid)=1

            if not exists (select 1 from MSmerge_identity_range where artid=@artid and is_pub_range=0 and sys.fn_MSmerge_islocalsubid(subid)=1)
                -- get the range that was allocated to this subscriber before upgrade
                if object_id('dbo.MSrepl_identity_range') is not NULL
                    select @range_begin = current_max - range, @range_end = current_max from dbo.MSrepl_identity_range where objid = @objid

                insert dbo.MSmerge_identity_range(subid, artid, range_begin, range_end, is_pub_range, max_used)
                    values(@subid, @artid, @range_begin, @range_end, 0, NULL)
                if @@error<>0
                    goto error

            if @subscriber_type = 1 and
                not exists (select 1 from MSmerge_identity_range where artid=@artid and is_pub_range=1 and sys.fn_MSmerge_islocalsubid(subid)=1)
                insert dbo.MSmerge_identity_range(subid, artid, is_pub_range, max_used)
                    values(@subid, @artid, 1, NULL)
                if @@error<>0
                    goto error

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

        /* Drop the default constraint on the rowguid column and create a new
        ** one that uses newsequential id. This will help improve performance.
        if @is_republisher = 0
            exec @retcode = sys.sp_MSaddguidcolumn @source_owner, @source_object
            if @@error <> 0 or @retcode <> 0
                goto error

        exec @retcode = sys.sp_MSaddmergetriggers @qualified_table_name
        if @retcode<>0 or @@error<>0
            goto error

        -- activate the article
        update dbo.sysmergearticles set status = 2 where artid = @artid and pubid = @pubid and status = 1
        update dbo.sysmergearticles set status = 6 where artid = @artid and pubid = @pubid and status = 5

        fetch next from #articles into @artid, @objid, @pubid

    if @is_republisher = 0 and @upgrade_metadata = 1
        drop table dbo.MSmerge_upgrade_in_progress
        if @@error<>0
            goto error

    select @upgrade_done = 1
    exec sys.sp_MSreleaseMergeUpgradeSubscriberAppLock
    return 0

    close #articles
    deallocate #articles
    raiserror(20691, 16, -1)
    exec sys.sp_MSreleaseMergeUpgradeSubscriberAppLock
    return 1

Last revision 2008RTM
See also

  sp_helpmergepublication (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash