May 16, 2012

sp_MShelpmergeconflictcounts (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MShelpmergeconflictcounts(nvarchar @publication_name
, nvarchar @publisher
, nvarchar @publisher_db
, int @logical_record_conflicts)

MetaData:

   
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, conflicts_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

No comments:

Post a Comment

Total Pageviews