Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_vupgrade_updatingpublicationarticle
    declare @retcode int
                ,@artid int
                ,@schema_option varbinary(8)
                ,@objid int
                ,@pubname sysname
                ,@queue_type int
                ,@artname sysname
                ,@distributor sysname
                ,@rpcsrvname sysname
                ,@distribdb sysname
                ,@fisdbpublished bit
                ,@dbname sysname
                ,@distproc nvarchar(517)
                ,@publishingservername sysname
                ,@allow_queued_tran int

    -- Do we have any publications to process
    if not exists (select * from syspublications where allow_sync_tran = 1 or allow_queued_tran = 1)
        return 0
    -- check if this db is set for tran publish
    -- no need to do refresh if not published
    select @fisdbpublished = case when (sys.fn_MSrepl_istranpublished(db_name(),0) = 1) then 1 else 0 end
    -- Check if we have distributor
    -- If we cannot locate the distributor
    -- no point in refreshing the subscriptions
    begin try
        exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor OUTPUT
                        ,@rpcsrvname = @rpcsrvname OUTPUT
                        ,@distribdb = @distribdb OUTPUT
        if @@error != 0 or @retcode != 0
            select @distributor = null
    end try
    begin catch
        -- There was some exception in sp_MSrepl_getdistributorinfo
        -- treat it as error
        select @distributor = null
    end catch
    -- Enumerate the updating publications
    begin tran
    save transaction updating_pub_upgrade

    declare #hcurarticles CURSOR LOCAL FAST_FORWARD FOR
        select a.artid, a.schema_option, a.objid, a.name, p.name, p.queue_type, p.allow_queued_tran
        from sysarticles as a join syspublications as p
            on a.pubid = p.pubid
            and objectproperty(a.objid, 'IsTable') = 1
            and (p.allow_sync_tran = 1 or p.allow_queued_tran = 1)
    open #hcurarticles
    fetch #hcurarticles into @artid, @schema_option, @objid, @artname, @pubname, @queue_type, @allow_queued_tran
    while @@fetch_status <> -1
        -- Does version column exist
        if not exists (select * from sys.columns where object_id = @objid and name = 'msrepl_tran_version')
            -- this is SQL 7.0 immediate updating article
            -- no need to do anything for this (same behavior as SQL2000 upgrade)
            -- skip this article
            fetch #hcurarticles into @artid, @schema_option, @objid, @artname, @pubname, @queue_type, @allow_queued_tran
        -- Identity specific processing
        if objectproperty(@objid, 'tablehasidentity') = 1 and (@allow_queued_tran = 1 or
        			exists (select name from sys.columns
                                where object_id = @objid and
                                    ColumnProperty(object_id, name, N'IsIdNotForRepl') = 1))
            -- set the schema bit 0x4
            select @schema_option = fn_replprepadbinary8(cast(@schema_option as bigint) | 0x4)
            update sysarticles set schema_option = @schema_option where artid = @artid
            if (@@error != 0)
                goto error
        -- generated metadata refresh
        if (@fisdbpublished = 1)
            -- Is this an MSMQ publication - convert it into SQL queue
            if (@queue_type = 1)
                -- refresh the data on publisher only
update syspublications set queue_type = 2 where name = @pubname
                if (@@error != 0)
                    goto error
                if (@distributor IS NOT NULL)
                    -- distributor is registered - refresh the metadata on distributor
                    select @dbname =  DB_NAME()
                            ,@distproc = QUOTENAME(@rpcsrvname) + N'.' + QUOTENAME(@distribdb) +
                            ,@publishingservername = publishingservername()

                    begin try
                        exec @retcode = @distproc
                                            @publisher = @publishingservername,
                                            @publisher_db = @dbname,
                                            @publication = @pubname,
                                            @property = N'queue_type',
                                            @value = '2'
                        if @@error != 0 or @retcode != 0
                            goto error
                    end try
                    begin catch
                        -- There was some exception in rpc execution
                        -- treat it as warning
                        raiserror(21542, 10, 1, @@error, @distproc)
                    end catch
            -- refresh the synctran procedures
            exec @retcode = sys.sp_articlesynctranprocs @publication = @pubname, @article = @artname
            if @@error != 0 or @retcode != 0
                goto error
            -- Refresh subscription if we can
            if (@distributor IS NOT NULL)
                -- Are there active subscriptions to this article
                if exists (select p.pubid from dbo.syspublications p join dbo.sysarticles a on a.pubid = p.pubid
                                    join dbo.syssubscriptions s on a.artid = s.artid
                            where a.artid = @artid
                                and (p.allow_queued_tran = 1 or p.allow_sync_tran = 1)
                                and s.status = 2  -- active
                                and s.sync_type != 2 -- not 'none'
                                and s.update_mode != 0 )
                    if (@queue_type = 1)
                        -- For MSMQ type publications, convert the update mode in syssubscriptions
                        -- Note: MSsubscriptions in distribution db get updated during the processing
                        -- of sp_MSchange_publication (which gets invoked above)
                        update dbo.syssubscriptions
                        set update_mode = case
                                when (update_mode = 2) then 4
                                when (update_mode = 3) then 5
                                when (update_mode = 7) then 6
                                else update_mode
                        where artid = @artid
                        if (@@error != 0)
                            goto error
                        -- post command to refresh subscriber update mode
                        exec @retcode = sys.sp_scriptupdmoderefresh @artid = @artid
                        if @@error != 0 or @retcode != 0
                            goto error

                    -- if the article's update command uses CALL syntax, change it to VCALL
                    update dbo.sysarticles
                        set upd_cmd = N'VCALL' + right(ltrim(upd_cmd), len(ltrim(upd_cmd)) - 4)
                        where artid = @artid and upper(left(ltrim(upd_cmd), 4)) = N'CALL'

                    -- this is necessary for upgrading from sql2000 since we changed logreader in yukon to
                    -- always send del/ins if unique column was updated and updatable subscriptions are involved
                    -- regardless of update being setbased or not, according to Kaushik this greatly simplifies
                    -- compensation logic in sync procs,
                    -- anyway, calling sp_MSsetfilteredstatus updates sys.tables.has_replication_filter as needed
                    exec sys.sp_MSsetfilteredstatus @objid
                    -- post commands to refresh metadata on subscriber
                    exec @retcode = sys.sp_MStran_autoproc @artid = @artid, @mode = 2
                    if @@error != 0 or @retcode != 0
                        goto error
                end -- if active subscriptions
            end -- if valid distributor
        end -- if db is published
        -- fetch next article
        fetch #hcurarticles into @artid, @schema_option, @objid, @artname, @pubname, @queue_type, @allow_queued_tran
    close #hcurarticles
    deallocate #hcurarticles
    -- all done
    commit tran
    return 0

    if @@trancount > 0
        rollback transaction updating_pub_upgrade
        commit transaction
    return 1

Last revision 2008RTM
See also

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