create procedure sys.sp_MScheckforexpiredmergesubscriptions
@mark_expired_as_failed_attach bit= 0
as
declare @publication sysname
declare @lastsyncdate datetime
declare @REPLICA_STATUS_BeforeRestore tinyint
declare @REPLICA_STATUS_AttachFailed tinyint
declare @retention int
declare @retention_period_unit tinyint
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
set @REPLICA_STATUS_BeforeRestore= 7
set @REPLICA_STATUS_AttachFailed= 6
-- Check for expired subscriptions this replica is subscribing to.
declare PC cursor local fast_forward for
select p.name,
p.retention,
p.retention_period_unit,
s.pubid,
s.subid,
s.last_sync_date
from dbo.sysmergepublications p join dbo.sysmergesubscriptions s
on p.pubid=s.pubid
where upper(s.subscriber_server) collate database_default=upper(@@servername) collate database_default and
isnull(p.retention,0)<>0 and
s.db_name=db_name() and
s.pubid<>s.subid and -- Skip subscriptions that represent publications.
s.status<>@REPLICA_STATUS_BeforeRestore and
s.last_sync_date < sys.fn_add_units_to_date(-p.retention,p.retention_period_unit, getdate())
open PC
fetch PC into @publication, @retention,@retention_period_unit, @pubid, @subid, @lastsyncdate
while @@fetch_status<>-1
begin
raiserror(21307, 10, -1, @publication)
if 1=@mark_expired_as_failed_attach
begin
update dbo.sysmergesubscriptions
set status= @REPLICA_STATUS_AttachFailed
where pubid=@pubid and subid=@subid
end
fetch PC into @publication, @retention,@retention_period_unit, @pubid, @subid, @lastsyncdate
end
close PC
deallocate PC
return 0