Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool.
SELECT TOP 1000 [subscriber_server]
,[db_name]
,SubStatus =
CASE [status]
WHEN 0 THEN ‘Inactive’
WHEN 1 THEN ‘Active’
WHEN 2 THEN ‘Deleted’
ELSE ‘N/A’
END
,convert(nvarchar(100), [last_sync_date], 100) as LastSyncedOn
,LastSyncStatus =
CASE [last_sync_status]
WHEN 0 THEN ‘All jobs are waiting to start’
WHEN 1 THEN ‘One or more jobs are starting’
WHEN 2 THEN ‘All jobs have executed successfully’
WHEN 3 THEN ‘At least one job is executing’
WHEN 4 THEN ‘All jobs are scheduled and idle’
WHEN 5 THEN ‘At least one job is attempting to execute after a previous failure’
WHEN 6 THEN ‘At least one job has failed to execute successfully’
ELSE ‘N/A’
END
,[last_sync_summary]
,ReplicationVersion =
CASE [replica_version]
WHEN 90 THEN ‘SQL Server 2005’
WHEN 100 THEN ‘SQL Server 2008’
ELSE ‘N/A’
END
FROM [MyDatabase].[dbo].[sysmergesubscriptions]
where subscriber_type=2
order by [last_sync_date] asc
Below might be needed as well for non-sysadmins
grant select on [sysmergesubscriptions] to public
More details on the system table used can be found by clicking here.
