April 27, 2012

sp_helpmergeconflictrows (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_helpmergeconflictrows(nvarchar @publication
, nvarchar @conflict_table
, nvarchar @publisher
, nvarchar @publisher_db
, int @logical_record_conflicts)

MetaData:

 create procedure sys.sp_helpmergeconflictrows(  
@publication sysname = '%',
@conflict_table sysname,
@publisher sysname = NULL,
@publisher_db sysname = NULL,
@logical_record_conflicts int = 0

)
as
set nocount on
declare @pubid uniqueidentifier
declare @cmd nvarchar(max)
declare @colname sysname
declare @colid int
declare @pubidstr nvarchar(38)
declare @retcode int
declare @result_table sysname
declare @guid uniqueidentifier

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

declare @quotedcolname nvarchar(258)

select @guid = newid()

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

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

select @colname=NULL

select @colid = min(column_id) from sys.columns where object_id = object_id(@conflict_table)
select @colname=name from sys.columns where object_id = object_id(@conflict_table) and column_id=@colid
select @cmd = 'select '

while (@colname is not NULL)
begin
if @colname <> 'origin_datasource_id'
begin
select @quotedcolname = quotename(@colname)
select @cmd = @cmd + ' ct.' + @quotedcolname + ','
end
select @colid = min(column_id) from sys.columns where object_id = object_id(@conflict_table) and column_id > @colid
select @colname = NULL
select @colname=name from sys.columns where object_id = object_id(@conflict_table) and column_id=@colid
end
select @cmd = @cmd + ' m.origin_datasource, m.conflict_type, m.reason_code, m.reason_text, m.pubid, m.MSrepl_create_time from'
select @cmd = @cmd + QUOTENAME(@conflict_table) + ' ct, MSmerge_conflicts_info m where ct.origin_datasource_id=m.origin_datasource_id
and m.conflict_type<>4 and ct.rowguidcol=m.rowguid '


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

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

set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
select @cmd = @cmd + ' and m.pubid = ' + @pubidstr
end
exec (@cmd)

if (@@ERROR <> 0)
return (1)
return (0)

No comments:

Post a Comment

Total Pageviews