Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelpmergeconflictcounts

  No additional text.


Syntax

create procedure sys.sp_MShelpmergeconflictcounts (
	@publication_name sysname = '%' ,
	@publisher		sysname = NULL,
	@publisher_db	sysname = NULL,
	@logical_record_conflicts int = 0)
as
begin

	set nocount on

	declare @aname		sysname
	declare @cmd		nvarchar(2000)
	declare @conflict_table sysname
	declare @count		integer
	declare @pubid		uniqueidentifier
	declare @pubidstr	nvarchar(38)
    declare @retcode	int

    -- Security check
    if 1 <> is_member('db_owner') and
	   (1 <> is_member('replmonitor') or is_member('replmonitor') is null)
	begin
		RAISERROR (15247, 11, -1)
		return (1)
	end

	if @publisher IS NULL
		select @publisher = publishingservername()
		
	if @publisher_db IS NULL
		select @publisher_db = db_name()

	if @publication_name <> '%'
		begin
			/*
			** Parameter Check:	 @publication.
			** Make sure that the publication exists.
			*/
			select @pubid = pubid from dbo.sysmergepublications
				where name = @publication_name and
					LOWER(publisher) = LOWER(@publisher) and
					publisher_db = @publisher_db
			if @pubid IS NULL
			BEGIN
				RAISERROR (20026, 16, -1, @publication_name)
				RETURN (1)
			END
			set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
		end

	-- make sure current db has merge publishing tables (true on both pub and sub)
	if object_id('sysmergearticles') is NULL
	begin
		raiserror( 18757, 16, -1 )
		return(1)
	end

	-- allow null conflict table name to handle case where there are delete conflicts but no update conflicts
	create table #result_list ( article sysname collate database_default, source_object sysname collate database_default, conflict_table sysname collate database_default null, guidcolname sysname collate database_default, centralized_conflicts integer, confl
icts_ucount integer, conflicts_dcount integer )
	create table #conflict_list ( article_name sysname collate database_default, conflicts_ucount integer, conflicts_dcount integer )
	create table #update_list ( article_name sysname collate database_default, conflicts_ucount integer )

	-- get delete counts
	if ( @publication_name = '%' )
		declare hCdcount CURSOR LOCAL FAST_FORWARD for
			select distinct a.name
				from dbo.MSmerge_conflicts_info d
				inner join dbo.sysmergepublications p on p.pubid = d.pubid
				inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
				where d.conflict_type in (4,7,8,12)
	else
		declare hCdcount CURSOR LOCAL FAST_FORWARD for
			select distinct a.name
				from dbo.MSmerge_conflicts_info d
				inner join dbo.sysmergepublications p on p.pubid = d.pubid
				inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
				where p.pubid = @pubid and d.conflict_type in (4,7,8,12)
	open hCdcount
	fetch hCdcount into @aname
	while ( @@fetch_status <> -1 )
	begin
		select @cmd = 'select N''' + @aname + ''', 0, count(*)
				from dbo.MSmerge_conflicts_info d
					inner join dbo.sysmergepublications p on p.pubid = d.pubid
					inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick '

		if @logical_record_conflicts = 0
		begin
			set @cmd = @cmd + 'where d.conflict_type in (4,7,8) and a.name = @aname'
		end
		else
		begin
			set @cmd = @cmd + 'where d.conflict_type in (4,7,8,12) and a.name = @aname'
		end

		if ( @publication_name <> '%' )
			select @cmd = @cmd + ' and p.pubid = ' + @pubidstr

		insert #conflict_list ( article_name, conflicts_ucount, conflicts_dcount )
			exec @retcode= dbo.sp_executesql @cmd, N'@aname sysname', @aname = @aname
		if @@error<>0 and @retcode<>0
		begin
			close hCdcount
			deallocate hCdcount
			return 1
		end

		fetch hCdcount into @aname
	end
	close hCdcount
	deallocate hCdcount

	-- get update counts
	if ( @publication_name = '%' )
		declare hCucount CURSOR LOCAL FAST_FORWARD for
			select distinct a.name, a.conflict_table
				from dbo.sysmergepublications p
				inner join dbo.sysmergearticles a on a.pubid = p.pubid
			where a.conflict_table is not null
	else
		declare hCucount CURSOR LOCAL FAST_FORWARD for
			select distinct a.name, a.conflict_table
				from dbo.sysmergepublications p
				inner join dbo.sysmergearticles a on a.pubid = p.pubid
			where a.conflict_table is not null
				and p.pubid = @pubid
	open hCucount
	fetch hCucount into @aname, @conflict_table
	while ( @@fetch_status <> -1 )
	begin
		select @cmd = N'select N''' + @aname + N''', count(*) from ' + QUOTENAME( @conflict_table ) + N' ct
		inner join MSmerge_conflicts_info m on ct.origin_datasource_id=m.origin_datasource_id and ct.rowguidcol=m.rowguid
		inner join dbo.sysmergepublications p on p.pubid = m.pubid '

	    -- Do not return logical record conflicts unless asked for.
	    if @logical_record_conflicts = 0
	    begin
    		select @cmd = @cmd + '
    		where m.conflict_type not in (11,12,13,14) '

		if ( @publication_name <> '%' )
			select @cmd = @cmd + ' and p.name = @publication_name'
    		
	    end
	    else
	    begin
		if ( @publication_name <> '%' )
			select @cmd = @cmd + ' where p.name = @publication_name'
	    end
	
		insert #update_list ( article_name, conflicts_ucount )
			exec @retcode= dbo.sp_executesql @cmd, N'@publication_name sysname', @publication_name = @publication_name
		if @@error<>0 and @retcode<>0
		begin
			close hCucount
			deallocate hCucount
			return 1
		end

		fetch hCucount into @aname, @conflict_table
	end
	close hCucount
	deallocate hCucount

	update #conflict_list set conflicts_ucount = isnull( ul.conflicts_ucount, 0 )
		from #conflict_list cl
		inner join #update_list ul on ul.article_name = cl.article_name

	delete #update_list
		from #update_list ul
		inner join #conflict_list cl on ul.article_name = cl.article_name

	insert #conflict_list
		select *, 0 from #update_list where conflicts_ucount > 0

	drop table #update_list
	select @cmd = N'insert into #result_list
			select distinct t.article_name, N'''' + quotename(schema_name( o.schema_id )) + ''.'' + quotename(o.name) + '''',' + ' a.conflict_table, c.name, p.centralized_conflicts, t.conflicts_ucount, t.conflicts_dcount
				from #conflict_list t
					inner join dbo.sysmergearticles a on a.name = t.article_name
					inner join dbo.sysmergepublications p on p.pubid = a.pubid
					inner join sys.objects o on o.object_id = a.objid
					inner join sys.columns c on c.object_id = o.object_id and c.is_rowguidcol = 1
				where (t.conflicts_ucount > 0 or t.conflicts_dcount > 0) and a.conflict_table is NOT NULL and p.name like @publication_name'

	exec @retcode= sys.sp_executesql @cmd, N'@publication_name sysname', @publication_name=@publication_name
	if @@error<>0 or @retcode<>0 return 1

	drop table #conflict_list

	select * from #result_list
	drop table #result_list

return (0)
end

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash