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)