create procedure sys.sp_replication_agent_checkup
@heartbeat_interval int = 10 -- minutes
as
declare @distribution_db sysname
declare @retstatus int
declare @proc nvarchar(255)
declare @retcode int
/*
** Security Check: require sysadmin
*/
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END
declare hCdistdbs CURSOR LOCAL FAST_FORWARD for
select name from msdb..MSdistributiondbs where
has_dbaccess(name) = 1
for read only
open hCdistdbs
fetch hCdistdbs into @distribution_db
while @@fetch_status <> -1
begin
select @proc = QUOTENAME(@distribution_db) + '.sys.sp_MSagent_retry_stethoscope'
execute @retcode = @proc
if @@error <> 0 or @retcode <> 0
begin
select @retstatus = 1
goto UNDO
end
select @proc = QUOTENAME(@distribution_db) + '.sys.sp_MSagent_stethoscope'
execute @retcode = @proc @heartbeat_interval
if @@error <> 0 or @retcode <> 0
begin
select @retstatus = 1
goto UNDO
end
fetch hCdistdbs into @distribution_db
end
set @retstatus = 0
UNDO:
close hCdistdbs
deallocate hCdistdbs
return (@retstatus)