Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSCleanupForPullReinitWithPubId

  No additional text.


Syntax
create procedure sys.sp_MSCleanupForPullReinitWithPubId (
    @pubid uniqueidentifier
    ) AS
    declare @retcode smallint
    declare @truncate smallint
    declare @artid uniqueidentifier

    begin transaction
    save tran cleanupforreinit

    -- Truncate if this is the only publication
    if object_id('MSmerge_rowtrack', 'U') is not NULL and
        exists (select * from dbo.sysmergesubscriptions where pubid = @pubid and subscription_type = 3) -- light weight subscription
    begin
        if not exists (select top 1 * from dbo.sysmergesubscriptions
                        where pubid <> @pubid and
                              status <> 7) -- REPLICA_STATUS_BeforeRestore
        begin
            truncate table dbo.MSmerge_rowtrack
            if @@error <> 0    goto Error
        end
        else if exists (select top 1 * from dbo.sysmergesubscriptions
                        where pubid = @pubid and
                              status <> 7) -- REPLICA_STATUS_BeforeRestore
        begin
            delete from dbo.MSmerge_rowtrack where tablenick in
                (select nickname from dbo.sysmergearticles where pubid = @pubid)
            if @@error <> 0    goto Error
        end

        -- reset watermarks
        update dbo.sysmergesubscriptions set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL, schemaversion = NULL, schemaguid = NULL
        where pubid = @pubid
        if @@ERROR<>0
            goto Error
        -- make sure we don't falsely use transitive recgen optimization
        update dbo.sysmergesubscriptions
            set replicastate= newid() where pubid=@pubid
        if @@error<>0 goto Error
    end
    else
    begin
        /*
        ** Make sure you NULL out gen_cur for other articles that share this table
        ** since we are deleting the genhistroy row for that generation
        */
        update dbo.sysmergearticles set gen_cur=NULL where gen_cur in
            (select generation from dbo.MSmerge_genhistory where pubid = @pubid)
        if @@ERROR<>0
            goto Error

        /*
        ** if the subscription for the given pubid is the last subscription (including pub subscribed to itself)
        ** then instead of expensive delete operations do a truncate table
        */
        if not exists (select * from dbo.sysmergesubscriptions where pubid<>@pubid and (sys.fn_MSmerge_islocalsubid(subid) = 1))
        begin
            truncate table dbo.MSmerge_genhistory
            if @@ERROR<>0
                goto Error
            truncate table dbo.sysmergesubsetfilters
            if @@ERROR<>0
                goto Error
            truncate table dbo.sysmergeschemachange
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_current_partition_mappings
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_past_partition_mappings
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_generation_partition_mappings
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_contents
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_tombstone
            if @@ERROR<>0
                goto Error
            truncate table dbo.MSmerge_metadataaction_request
            if @@ERROR<>0 goto Error
            exec @retcode= sys.sp_MSdrop_tempgenhistorytable @pubid
            if @@error<>0 or @retcode<>0 goto Error
        end
        else
        begin
            declare @publication_number smallint
            select @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

            delete from dbo.MSmerge_genhistory where pubid = @pubid
            if @@ERROR<>0
                goto Error
            delete from dbo.sysmergesubsetfilters where pubid=@pubid
            if @@ERROR<>0
                goto Error
            delete from dbo.sysmergeschemachange where pubid = @pubid
            if @@ERROR<>0
                goto Error
            delete from dbo.MSmerge_current_partition_mappings where publication_number = @publication_number
            if @@ERROR<>0
                goto Error
            delete from dbo.MSmerge_past_partition_mappings where publication_number = @publication_number
            if @@ERROR<>0
                goto Error
            delete from dbo.MSmerge_generation_partition_mappings where publication_number = @publication_number
            if @@ERROR<>0
                goto Error
            delete from dbo.MSmerge_contents where tablenick in (select nickname from dbo.sysmergearticles where pubid=@pubid)
            if @@ERROR<>0
                goto Error
            delete from dbo.MSmerge_tombstone where tablenick in (select nickname from dbo.sysmergearticles where pubid=@pubid)
            if @@ERROR<>0
                goto Error
            exec @retcode= sys.sp_MSdrop_tempgenhistorytable @pubid
            if @@error<>0 or @retcode<>0
                goto Error
        end

        update dbo.sysmergesubscriptions set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL, schemaversion = NULL, schemaguid = NULL
                where pubid = @pubid
        if @@ERROR<>0
            goto Error
        -- make sure we don't falsely use transitive recgen optimization
        update dbo.sysmergesubscriptions
            set replicastate= newid() where pubid=@pubid
        if @@error<>0 goto Error

        /* Watermarks wrt to alternate publisher must also be reset */
        exec @retcode= sys.sp_MSresetwatermarksforalternatepublishers @pubid = @pubid
        if @@error<>0 or @retcode<>0 goto Error

        /* Clean up any logical record views */
        exec @retcode = sys.sp_MSreset_logical_record_views @pubid = @pubid
        if @@error<>0 or @retcode<>0 goto Error

        /* Clean up the articles for this publication, and delete the row */
        select @artid = artid FROM dbo.sysmergearticles WHERE pubid = @pubid
        while @artid is not null
        begin
            if not exists (select * from dbo.sysmergearticles WHERE artid = @artid and pubid <> @pubid)
            begin
                exec @retcode=sys.sp_MSarticlecleanup @pubid, @artid, 1, 1
                if @retcode<>0 or @@ERROR<>0
                begin
                    goto Error
                end
            end
            delete from dbo.sysmergepartitioninfo where artid = @artid and pubid = @pubid
            delete from dbo.sysmergearticles where artid = @artid and pubid = @pubid
            set @artid = NULL
            select @artid = artid FROM dbo.sysmergearticles WHERE pubid = @pubid
        end

        if not exists (select * from dbo.sysmergearticles)
        begin
			execute @retcode=sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
			if @@ERROR <> 0 or @retcode <> 0 goto Error
		end
    end

    commit tran

    return (0)

Error:
    rollback tran cleanupforreinit
    commit tran
    return (1)

 
Last revision 2008RTM
See also

  sp_MSCleanupForPullReinit (Procedure)
sp_MSpurgepartialmergesnapshot (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