June 26, 2009 at 11:52 am
experts , can you help me out to remove duplicates from the below query , i am inserting tracer token every minute and have 2 subscribers ,below query runs fine and gives correct result but as soon as i un-comment the commented columns to bring the required result it gives duplicates...
use distribution
SELECT
--MM.Publisher AS [Publisher Server],
--MI.subscriber AS [Subscriber Server],
--MM.publisher_db AS [Publisher DB],
DATEDIFF(ss, TT.publisher_commit,TH.subscriber_commit) AS [Publisher to Subscriber Latency in Minutes]
--,DATEDIFF(mi, TH.subscriber_commit, (GETDATE())) AS [Realized Subscriber Latency in Minutes]
FROM
MSTracer_tokens TT(NOLOCK)
JOIN MSTracer_history TH(NOLOCK)
ON TT.tracer_id = TH.parent_tracer_id
--JOIN MSreplication_monitordata MM(NOLOCK)
--ON TT.publication_id=MM.publication_id
--AND mm.agent_type = 3
--JOIN MSsubscriber_info MI
--ON MM.publisher=MI.publisher
JOIN (SELECT MAX(tracer_id)s ,publication_id FROM MSTracer_tokens (NOLOCK)
JOIN MSTracer_history(NOLOCK)
ON tracer_id=parent_tracer_id
WHERE
subscriber_commit IS NOT NULL
GROUP BY
publication_id)xyz
ON TT.tracer_id = xyz.s
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply