Sunday, February 19, 2012

Checking Merge agent status in SQL2005

Hi sql gurus,
Is there any way to check the status of the merge agent job per subscriber
in SQL 2005?
Below is the same check for SQL 2000:
use distribution
select runstatus from MSmerge_history as h
where h.timestamp in (select max(timestamp) from MSmerge_history where
agent_id =
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'<subscriber_server_name>')) )
I discovered the solution
use distribution
select mh.*, isnull(ms.runstatus,0) as runstatus
from dbo.MSmerge_history mh with (READPAST), dbo.MSmerge_sessions ms with
(READPAST)
where mh.timestamp in (select max(timestamp) from MSmerge_history where
agent_id in
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'subscriber_server_name'))) and ms.agent_id = mh.agent_id

No comments:

Post a Comment