create procedure sys.sp_replmonitorhelppublisherhelper
(
@publisher sysname = NULL -- pubisher - null means all publisher
,@refreshpolicy tinyint = 0 -- 0 = default cache refresh, 1 = optimistic force refresh, 2 = non-optimistic force refresh
)
as
begin
set nocount on
declare @retcode int
,@status int
,@warning int
,@publicationcount int
,@returnstamp datetime
-- security: Has to be executed from distribution database
if sys.fn_MSrepl_isdistdb (db_name()) != 1
begin
raiserror (21482, 16, -1, 'sp_replmonitorhelppublisher', 'distribution')
return 1
end
-- publisher data table must exist
if object_id('tempdb..#tmp_replication_publisherdata') is null
begin
raiserror(20507, 16, 5, '#tmp_replication_publisherdata', 'tempdb')
return 1
end
-- create temp table to get the monitoring data
create table #tmp_replication_monitordata
(
publication_id int
,publisher sysname null
,publisher_srvid int
,publisher_db sysname null
,publication sysname null
,publication_type int
,agent_type int
,agent_name sysname
,job_id uniqueidentifier
,status int
,isagentrunningnow bit
,warning int
,last_distsync datetime
,agentstoptime datetime
,distdb sysname null
,retention int
,time_stamp datetime null
,worst_latency int
,best_latency int
,avg_latency int
,cur_latency int
,mergePerformance int
,mergelatestsessionrunduration int
,mergelatestsessionrunspeed float
,mergelatestsessionconnectiontype int
,retention_period_unit tinyint
)
if @@error != 0
begin
raiserror(20507, 16, 1, '#tmp_replication_monitordata', 'tempdb')
return 1
end
-- build indices
create nonclustered index nc1tmp_replication_monitordata
on #tmp_replication_monitordata(publisher_srvid)
create nonclustered index nc2tmp_replication_monitordata
on #tmp_replication_monitordata(agent_type)
create nonclustered index nc4tmp_replication_monitordata
on #tmp_replication_monitordata(publisher)
create nonclustered index nc5tmp_replication_monitordata
on #tmp_replication_monitordata(publication, publisher_db)
create nonclustered index nc6tmp_replication_monitordata
on #tmp_replication_monitordata(agent_name)
if (@@error != 0)
return 1
-- get refresh data
exec @retcode = sys.sp_replmonitorrefreshdata @publisher = @publisher, @refreshpolicy = @refreshpolicy
if @@error != 0 or @retcode != 0
return 1
-- Initialize the @publisherdata
insert into #tmp_replication_publisherdata(
publisher
,distribution_db)
select distinct upper(publisher)
,distdb
from #tmp_replication_monitordata
if @@error != 0
return 1
-- update the other columns in #tmp_replication_publisherdata
declare #hcrefreshmonitor cursor local fast_forward for
select publisher from #tmp_replication_publisherdata
where distribution_db = db_name()
open #hcrefreshmonitor
fetch #hcrefreshmonitor into @publisher
while (@@fetch_status != -1)
begin
-- overall_status
-- overall_warning
select @status = max(status)
,@warning = max(isnull(warning,0))
,@returnstamp = max(isnull(time_stamp,0))
from #tmp_replication_monitordata
where publisher = @publisher
-- publicationcount
-- Logreader/Queuereader adds common agent entry for
-- more than one publication and names the publication ALL.
-- When counting publications we skip these entries
select @publicationcount = count(distinct publication_id)
from #tmp_replication_monitordata
where publisher = @publisher
and agent_type not in (2,9)
-- update
update #tmp_replication_publisherdata
set status = @status
,warning = @warning
,publicationcount = @publicationcount
,returnstamp = left(replace(replace(replace(replace(convert(nvarchar, @returnstamp, 121), '-', ''), ' ', ''), ':', ''), '.', ''), 16)
where publisher = @publisher
if @@error != 0
return 1
-- get next row
fetch #hcrefreshmonitor into @publisher
end
close #hcrefreshmonitor
deallocate #hcrefreshmonitor
-- all done
return 0
end