Home  |  Articles  |  Scripts  |  Consultancy  |  FAQs  |  Links

create Proc spBrowseMergeChanges (@articlename sysname)
/*****************************************************
Created by: Paul Ibison (www.replicationanswers.com)
Modified by: Patrick Molijn (patrick@molijn_nospam_remove_this_.net) to deal with rowguids not called ROWGUID - 23/5/2005
Modified by: Patrick Molijn (patrick@molijn_nospam_remove_this_.net) for SQL 2005 - 06/5/2006
Modified by: Patrick Molijn (patrick@molijn_nospam_remove_this_.net) utilising genstatus of SQL 2005 - 27/5/2006
Date: 27th May 2006
Use: To enumerate all the changes at a subscriber which haven't yet been merged.
******************************************************/
as
begin
-- Fetch the rowguidcol of the article
declare @ROWGUIDCol sysname
select @ROWGUIDCol = c.[name]
from syscolumns c
where c.id = object_id(@articlename)
and columnproperty(object_id(@articlename), c.[name], 'IsRowGUIDCol') = 1

if @ROWGUIDCol is null
return -- Can't determine the rowguidcol so return null

exec ('SELECT ''Ins/Upd'' as Type,
mc.rowguid AS RowGuid,
smp.publisher AS Publisher, 
smp.name AS Publication, 
sma.name AS Article,' + @articlename + '.* 
from dbo.MSMerge_genhistory mh with (nolock)
inner join dbo.MSmerge_contents mc with (nolock) on (mc.generation = mh.generation)
inner join ' + @articlename + ' with (nolock) ON mc.rowguid = ' + @articlename + '. ' + @ROWGUIDCol 
+ ' inner join dbo.sysmergearticles sma with (nolock) on (sma.nickname = mc.tablenick)
-- Our changes
inner join dbo.sysmergesubscriptions sms with (nolock) on (sms.pubid = sma.pubid and sms.subid != sms.pubid )
-- Who do we need to sent it to
inner join dbo.sysmergesubscriptions smReceiver with (nolock) on (smReceiver.subid = sms.pubid)
inner join dbo.sysmergepublications smp with (nolock) on (smp.pubid = smReceiver.pubid)
inner join dbo.MSmerge_replinfo mri with (nolock) on (sms.subid = mri.repid )
where mh.genstatus = 0
and sma.name = ''' + @articlename + '''

union all
SELECT ''Del'', 
mt.rowguid, 
smp.publisher, 
smp.name, 
sma.name, ' + @articlename + '.*
from dbo.MSMerge_genhistory mh with (nolock)
inner join dbo.MSmerge_tombstone mt with (nolock) on (mt.generation = mh.generation)
inner join dbo.sysmergearticles sma with (nolock) on (sma.nickname = mt.tablenick)
-- Our changes
inner join dbo.sysmergesubscriptions sms with (nolock) on (sms.pubid = sma.pubid and sms.subid != sms.pubid)
-- Who do we need to sent it to
inner join dbo.sysmergesubscriptions smReceiver with (nolock) on (smReceiver.subid = sms.pubid)
inner join dbo.sysmergepublications smp with (nolock) on (smp.pubid = smReceiver.pubid)
inner join dbo.MSmerge_replinfo mri with (nolock) on (sms.subid = mri.repid )
left outer join ' + @articlename + ' on (mt.rowguid = ' + @articlename + '.' + @ROWGUIDCol + ')'
+ ' where mh.genstatus = 0 
and sma.name = ''' + @articlename + '''')
end
go

create Proc spBrowseMergeChanges (@articlename sysname)
/*****************************************************
Created by: Paul Ibison (www.replicationanswers.com) Date: 7th March 2005
Modified by: Patrick Molijn (patrick@molijn_nospam_remove_this_.net) to deal with rowguids not called ROWGUID - 23/5/2005

Modified by Daniel Kristensen 29th May 2006 to use 

MSmerge_contents.generation = sysmergearticles.gen_cur instead of MSmerge_contents.generation > MSmerge_replinfo.sentgen.

Use: To enumerate all the changes at a subscriber which haven't yet been merged.
******************************************************/
as
begin
-- Fetch the rowguidcol of the article
declare @ROWGUIDCol sysname
select @ROWGUIDCol = c.[name]
from syscolumns c
where c.id = object_id(@articlename)
and columnproperty(object_id(@articlename), c.[name], 'IsRowGUIDCol') = 1

if @ROWGUIDCol is null
return -- Can't determine the rowguidcol so return null

exec ('SELECT ''Ins/Upd'' as Type,
MSmerge_contents.rowguid AS RowGuid,
sysmergepublications.publisher AS Publisher, 
sysmergepublications.name AS Publication, 
sysmergearticles.name AS Article,' 
+ @articlename + '.* 
FROM MSmerge_contents with (nolock)
INNER JOIN ' + @articlename + ' with (nolock) ON MSmerge_contents.rowguid = ' + @articlename + '. ' + @ROWGUIDCol 
+ ' INNER JOIN sysmergearticles with (nolock) ON MSmerge_contents.tablenick = sysmergearticles.nickname 
INNER JOIN sysmergesubscriptions with (nolock) ON sysmergearticles.pubid = sysmergesubscriptions.partnerid 
INNER JOIN sysmergepublications with (nolock) ON sysmergesubscriptions.pubid = sysmergepublications.pubid 
INNER JOIN MSmerge_replinfo with (nolock) ON sysmergesubscriptions.subid = MSmerge_replinfo.repid 
AND MSmerge_contents.generation = sysmergearticles.gen_cur 
WHERE sysmergearticles.name = ''' + @articlename + '''
union all
SELECT ''Del'', 
MSmerge_tombstone.rowguid, 
sysmergepublications.publisher, 
sysmergepublications.name, 
sysmergearticles.name, ' + @articlename + '.*
FROM MSmerge_tombstone with (nolock) 
INNER JOIN sysmergearticles with (nolock) ON MSmerge_tombstone.tablenick = sysmergearticles.nickname 
INNER JOIN sysmergesubscriptions with (nolock) ON sysmergearticles.pubid = sysmergesubscriptions.partnerid 
INNER JOIN sysmergepublications with (nolock) ON sysmergesubscriptions.pubid = sysmergepublications.pubid 
INNER JOIN MSmerge_replinfo with (nolock) ON sysmergesubscriptions.subid = MSmerge_replinfo.repid 
AND MSmerge_tombstone.generation = sysmergearticles.gen_cur 
LEFT OUTER JOIN ' + @articlename + ' ON MSmerge_tombstone.rowguid = ' + @articlename + '.' + @ROWGUIDCol
+ ' WHERE sysmergearticles.name = ''' + @articlename + '''')
end
go