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