Monday, March 12, 2012

How to find out wether a row has been replicated?

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 :)

No comments:

Post a Comment