Replication Query Problem

SELECT DISTINCT MH.agent_id,
MA.subscriber_name,
MH.publisher_insertcount,
MH.publisher_updatecount,
MH.publisher_deletecount,
MH.publisher_conflictcount,
MH.subscriber_insertcount,
MH.subscriber_updatecount,
MH.subscriber_deletecount,
MH.subscriber_conflictcount,
MAX(start_time) AS LastSync
FROM Distribution.dbo.MSMerge_History MH INNER JOIN
Distribution.dbo.MSMerge_Agents MA
ON (MA.id = MH.agent_id)
WHERE runstatus = 2
AND LEN(subscriber_name) > 0
AND MA.publication = 'JCS'
GROUP BY agent_id, subscriber_name, runstatus, publisher_insertcount,
publisher_updatecount,
publisher_deletecount, publisher_conflictcount,
subscriber_insertcount, subscriber_updatecount,
subscriber_deletecount, subscriber_conflictcount
ORDER BY subscriber_name ASC
When I run the above query, I get back the expected results. However, I am
getting two rows for one subscriber in particular, and when I executed the
Query in QA, I saw that the agent_id for both of those rows is the same.
Both rows show the replication as being successful; the times ran were only
two minutes apart. What am I missing? I was under the understanding that
specifying DISTINCT in your SELECT ensured that you would only get unique,
non-matching rows returned. You can also see that I am selecting the most
recent (MAX) start_time in my query. Any helpers out there as to why this
would occur?
Any help is MUCH appreciated!
Best Regards,
Brad
Is all of the data exactly the same including the insert/update/delete
counts?
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||No, the SyncDate and counts are different for each row. But I was under the
impression that using MAX, as well as DISTINCT would ensure that I would not
get duplicate rows (note that when I get multiple rows, the agent_id for the
duplicate rows is the exact same).
Any help with/explanation regarding this would be greatly appreciated Mike!
Best Regards,
Brad
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:uepgBvGIEHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is all of the data exactly the same including the insert/update/delete
> counts?
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>