Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_expired_mergesubscription

  No additional text.


Syntax
create procedure sys.sp_MSdrop_expired_mergesubscription
AS
/*
** This stored procedure is to periodically check the status of all the subscriptions
** of every merge publication. If any of them is out-of-date, i.e., has lost contact
** with publisher for a certain length of time, we can declare the death of that replica
** and cleanup their traces at the publisher side
*/
declare @subscription_type  int
declare @subscriber_type    int
declare @sub_type           nvarchar(5)
declare @publication        sysname
declare @pubid              uniqueidentifier
declare @subid              uniqueidentifier
declare @status             tinyint
declare @subscriber         sysname
declare @subscriber_id      int
declare @subscriber_db      sysname
declare @publisher_db       sysname
declare @retention          int -- in some time unit
declare @retention_period_unit tinyint -- the time unit
declare @retcode            smallint
declare @recgen             bigint
declare @sentgen            bigint
declare @recent_merge       datetime
declare @minus_retention2   datetime
declare @minus_retention    datetime
declare @send_ts            datetime
declare @receive_ts         datetime

/*
** Security Check
*/
    EXEC @retcode = sys.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

/* we do not check return code here because we can tolerate any failure here */
exec @retcode = sys.sp_MScleanup_conflict_table
if @@ERROR<>0 or @retcode<>0
    return (1)

exec @retcode = sys.sp_MSdrop_expired_mergesubscription90
if @@ERROR<>0 or @retcode<>0
    return (1)

declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT p.name, p.pubid, p.retention, p.retention_period_unit
	from dbo.sysmergepublications p, dbo.sysmergesubscriptions s
        where s.subid=p.pubid and s.pubid=p.pubid and p.snapshot_ready=1 for read only
    open PC
    fetch PC into @publication, @pubid, @retention, @retention_period_unit

WHILE (@@fetch_status <> -1)
BEGIN
    /* Compute the retention period cutoff dates per publication */
    select @minus_retention2 = sys.fn_add_units_to_date(-@retention * 2, @retention_period_unit, getdate())
    select @minus_retention = sys.fn_add_units_to_date(-@retention, @retention_period_unit, getdate())
    if @retention is not NULL and @retention > 0
    begin
        declare SC CURSOR LOCAL FAST_FORWARD for
            select subscriber_server, db_name, subid, status, subscription_type, subscriber_type, last_sync_date
                from dbo.sysmergesubscriptions
                where pubid = @pubid
                and pubid<>subid
                and replica_version < 90 -- do this for downlevel subscribers only.
                for read only
        open SC
        fetch SC into @subscriber, @subscriber_db, @subid, @status, @subscription_type, @subscriber_type, @recent_merge
        WHILE (@@fetch_status <> -1)
        BEGIN
            if @subscription_type = 0
                select @sub_type = 'push'
            else
                select @sub_type = 'pull'

            /*
            select @receive_ts = coldate from dbo.MSmerge_genhistory
            	where guidsrc = (select recguid from dbo.sysmergesubscriptions where subid = @subid)
            select @sentgen=sentgen from dbo.sysmergesubscriptions where subid=@subid
            select @send_ts = coldate from dbo.MSmerge_genhistory where generation=@sentgen
            if @receive_ts>@send_ts select @recent_merge = @receive_ts
               else select @recent_merge = @send_ts
            */

            -- we will now check the last sync time in sysmergesubscriptions to determine if the subscription should be expired
            if @recent_merge is not NULL and @recent_merge<@minus_retention
            begin
                -- for global subscriptions call dropmergesubscription
                -- for the rest set the status of the subscription to delted or expired
                if @subscriber_type = 1
                begin
                    exec @retcode = sys.sp_dropmergesubscription
                            @publication = @publication,
                            @subscriber = @subscriber,
                            @subscriber_db = @subscriber_db,
                            @subscription_type = @sub_type
                    if @retcode <>0 or @@ERROR<>0
                        goto FAILURE
                end
                else
                begin
                    update dbo.sysmergesubscriptions set status=2 where subid=@subid
                    IF @@ERROR<>0
                        GOTO FAILURE
                end
                raiserror(14157, 10, -1, @subscriber, @publication)
            end

            -- do not clean up anonymous since it can come back and think that it is not expired if we clean it up. can cleanup local and global.
            -- anonymous is subscriber_type 3
            if @recent_merge is not NULL and @recent_merge<@minus_retention2 and (@subscriber_type = 1 or @subscriber_type = 2)
            begin
                -- delete supportability settings for the subscriptions that we are about to delete.
                delete from dbo.MSmerge_supportability_settings where subid = @subid
                delete from dbo.MSmerge_log_files where subid = @subid

                delete from dbo.sysmergesubscriptions where subid = @subid --delete the row in dbo.sysmergesubscription
                if @@ERROR<>0
                    goto FAILURE

                exec sys.sp_MScleanup_subscriber_history @subid=@subid
                if @@ERROR<>0
                    goto FAILURE

                delete from dbo.MSmerge_replinfo where repid = @subid
                if @@ERROR<>0
                    goto FAILURE

                -- If the only remaining subscriptions are old entries (before restore),
                -- we remove them now.
                if not exists (select * from dbo.sysmergesubscriptions
                                where status <> 7) -- REPLICA_STATUS_BeforeRestore
                begin
                    delete from dbo.sysmergesubscriptions
                    truncate table dbo.MSmerge_supportability_settings
                    truncate table dbo.MSmerge_log_files
                    truncate table dbo.MSrepl_errors
                    truncate table dbo.MSmerge_history
                    truncate table dbo.MSmerge_articlehistory
                    truncate table dbo.MSmerge_sessions
                    delete from dbo.MSmerge_replinfo
                end
            end
            fetch SC into @subscriber, @subscriber_db, @subid, @status, @subscription_type, @subscriber_type, @recent_merge
        END
        CLOSE SC
        DEALLOCATE SC
    end
    fetch PC into @publication, @pubid, @retention, @retention_period_unit
END
CLOSE PC
DEALLOCATE PC
return (0)

FAILURE:
    close SC
    deallocate SC
    close PC
    deallocate PC
    return (1)

 
Last revision 2008RTM
See also

  sp_expired_subscription_cleanup (Procedure)
sp_MSadd_merge_anonymous_agent (Procedure)
sp_MSdrop_expired_mergesubscription90 (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MShelp_merge_agentid (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