Dear ppl,
In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?
Regards
Nabeel-
The procedure below should get you started on being able to tell if a row has been sent to a subscriber, all you need to do is provide the rowguid and publication name.
use <PUB_DB_NAME>
go
create procedure ASubHasIt(@.pubname sysname, @.row uniqueidentifier)
as
declare @.tablenick int
declare @.maxsentgen int
select @.maxsentgen = max(sentgen), @.tablenick = max(nickname) from (sysmergesubscriptions sms join sysmergepublications smp on sms.pubid = smp.pubid) join sysmergearticles sma on sma.pubid=smp.pubid where smp.name='PubName' and sms.sentgen IS NOT NULL
if exists (select * from MSmerge_genhistory gh join MSmerge_contents mc on mc.generation = gh.generation where gh.generation > @.maxsentgen and mc.rowguid = @.row)
begin
print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has NOT been sent to a subscriber'
end
else
begin
print 'Row ' + CONVERT(nvarchar(max), @.row) + ' has been sent to a subscriber'
end
go
Example:
exec ASubHasIt @.pubname='PubName', @.row='8B348C04-B3A9-DB11-AEA6-000BDBD0506C'
Hope this helps!
-Phil Piwonka
|||excellent...cheers mate :)