MicrosoftHi Liwei
Nice post. I think that one thing should be cleared. If you have setup only merge replication, subscriber_id column will be shown like NULL and then the scripts should be modified (replace inner join by left join in ). If you setup transactional replication then a new row will be inserted in sys.servers table with the subscriber data (id, etc).
use distribution---in distributor server
if not exists(select 1 from sys.tables where name ='MSreplservers')
begin
select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.name as publisherName ,ss.name as subscriber,a.subscriber_db, a.local_job From MSmerge_agents a
inner join sys.servers sp on a.publisher_id=sp.server_id--publisher
left join sys.servers ss on a.subscriber_id =ss.server_id--subscriber
left join msdb..sysjobs job on job.job_id=a.job_id
end
else
begin
select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.srvname as publisherName ,ss.srvname as subscriber,a.subscriber_db, a.local_job From MSmerge_agents a
inner join msreplservers sp on a.publisher_id=sp.srvid--publisher
inner join msreplservers ss on a.subscriber_id =ss.srvid--subscriber
left join msdb..sysjobs job on job.job_id=a.job_id
end
