Checks a SQL Table for Replication Status.
Use the below code to create the table required for the replication information to reside in.
CREATE TABLE PublicationInfo
(
publisher_db sysname,
publication sysname,
publication_id int,
publication_type int,
status int,
warning int,
worst_latency int,
best_latency int,
average_latency int,
last_distsync datetime,
retention int,
latencythreshold int,
expirationthreshold int,
agentnotrunningthreshold int,
subscriptioncount int,
runningdistagentcount int,
snapshot_agentname sysname,
logreader_agentname sysname,
qreader_agentname sysname NULL,
worst_runspeedPerf int,
best_runspeedPerf int,
average_runspeedPerf int,
retention_period_unit int,
publisher sysname
)
Use the beow code in a scheduled SQL Job on the Distribution database server to populate the table with data needed every X minutes. ( I run it every 4 minutes, runs in under 1 second)
TRUNCATE TABLE PublicationInfo
EXEC sp_replmonitorhelppublication
INSERT INTO PublicationInfo
Then customise the template variables with the names of your own publications.
We have approx 15 publications, just create a new component for each one to report individually on your own publications.