<babu.mani@gmail.com> wrote in message
news:1148527975.200534.247690@j73g2000cwa.googlegroups.com...
>I have changed Paul Ibison's stored proc a little bit. This proc
> should wok for both Central and Branch database and lists for all
> replicated articles, so parameter is optional. Also it eliminates
> duplication.
>
> Let me know if there is any flaw.
>
> CREATE PROC PBS_sp_ShowPendingMergeChanges (@p_articlename sysname =
> '%')
>
> AS
>
> BEGIN
>
> -- Fetch the rowguidcol of the article
> declare @ROWGUIDCol sysname
> declare @sql varchar(8000)
> declare @articlename sysname
>
> create table #t
> (
> Publisher varchar(50),
> Publication varchar(50),
> Article varchar(50),
> Type varchar(15),
> PrimaryKey uniqueidentifier
> )
>
> -- Declare cursor to report for all articles
> DECLARE PBS_cs_mergearticles CURSOR LOCAL FAST_FORWARD FOR
> SELECT ltrim(rtrim(name)) FROM sysmergearticles where name like
> @p_articlename
>
> OPEN PBS_cs_mergearticles
>
> FETCH NEXT FROM PBS_cs_mergearticles INTO @articlename
>
> WHILE @@FETCH_STATUS = 0 BEGIN
>
> 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
>
> set @sql =
> 'SELECT distinct ' +
> ' sysmergepublications.publisher AS Publisher, ' +
> ' sysmergepublications.name AS Publication, ' +
> ' sysmergearticles.name AS Article, ' +
> ' ''Ins/Upd'' as Type, ' +
> ' MSmerge_contents.rowguid AS PrimaryKey ' +
> -- '[' + @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 ' +
> ' AND sysmergesubscriptions.db_name = db_name() ' +
> ' INNER JOIN sysmergepublications with (nolock) ' +
> ' ON sysmergesubscriptions.pubid = sysmergepublications.pubid ' +
> ' WHERE ' +
> ' sysmergearticles.name = ''' + @articlename + ''' ' +
> ' AND sysmergearticles.gen_cur = MSmerge_contents.generation ' +
>
> ' UNION ALL ' +
>
> ' SELECT distinct ' +
> ' sysmergepublications.publisher, ' +
> ' sysmergepublications.name, ' +
> ' sysmergearticles.name, ' +
> ' ''Del'', ' +
> ' MSmerge_tombstone.rowguid ' +
> -- '[' + @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 ' +
> ' AND sysmergesubscriptions.db_name = db_name() ' +
> ' INNER JOIN sysmergepublications with (nolock) ' +
> ' ON sysmergesubscriptions.pubid = sysmergepublications.pubid ' +
> ' LEFT OUTER JOIN [' + @articlename + ']' +
> ' ON MSmerge_tombstone.rowguid = [' + @articlename + '].' +
> @ROWGUIDCol +
> ' WHERE sysmergearticles.name = ''' + @articlename + '''' +
> ' AND sysmergearticles.gen_cur = MSmerge_tombstone.generation '
>
> INSERT INTO #t
> Exec (@sql)
>
>
> FETCH NEXT FROM PBS_cs_mergearticles INTO @articlename
>
> END
>
> CLOSE PBS_cs_mergearticles
> DEALLOCATE PBS_cs_mergearticles
>
> Select * from #t order by publisher, publication, article, type,
> primarykey
>
> END
>
> GO
>