-- Requires Certificate signature for catalog access
create procedure sys.sp_MSgenerate_articlechangemembershipevaluation_proc (@pubid uniqueidentifier, @article_nickname int)
as
begin
declare @partition_view_name nvarchar(270),
@retcode int,
@artid uniqueidentifier,
@guidstr nvarchar(40),
@pubidstr nvarchar(40),
@membership_eval_proc_name nvarchar(130),
@objid int,
@rgcol nvarchar(270),
@dbname nvarchar(130),
@cmd nvarchar(max),
@quoted_membership_eval_proc_name nvarchar(258)
select @artid = artid, @objid = objid from dbo.sysmergearticles
where nickname = @article_nickname and pubid = @pubid
select @partition_view_name = quotename(object_name(partition_view_id)), @membership_eval_proc_name = membership_eval_proc_name
from dbo.sysmergepartitioninfo
where artid = @artid and pubid = @pubid
if @partition_view_name is null
return 0 -- too early. sp_MSpublicationview has not been called yet.
if @membership_eval_proc_name is not null
begin
if object_id (@membership_eval_proc_name) is not null
begin
set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)
exec ('drop procedure dbo.' + @quoted_membership_eval_proc_name)
end
update dbo.sysmergepartitioninfo set membership_eval_proc_name = NULL
where artid = @artid and pubid = @pubid
end
select @rgcol = quotename(name) from sys.columns where object_id = @objid and is_rowguidcol = 1
select @dbname = db_name()
exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out
if @@ERROR <>0 OR @retcode <>0 return (1)
exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
if @@ERROR <>0 OR @retcode <>0 return (1)
declare @publication_number smallint
select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid
select @membership_eval_proc_name = 'MSmerge_evalmembership_sp_' + sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)
if object_id (@membership_eval_proc_name) is not null
begin
set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)
exec ('drop procedure dbo.' + @quoted_membership_eval_proc_name)
if @@error<>0 return 1
end
set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)
select @cmd = N'create procedure dbo.' + @quoted_membership_eval_proc_name + ' @partition_id int = NULL, @rowguid uniqueidentifier = NULL, @marker uniqueidentifier = NULL as
begin
if ({ fn ISPALUSER(''' + convert(nvarchar(36),@pubid) + ''') } <> 1)
begin
RAISERROR (14126, 11, -1)
return 1
end
if @partition_id is not null
begin
insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from dbo.MSmerge_contents mc
JOIN ' + @partition_view_name + ' v
ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
and mc.rowguid = v.' + @rgcol + '
and v.partition_id = @partition_id
where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
and mcpm.tablenick = mc.tablenick
and mcpm.rowguid = mc.rowguid
and mcpm.partition_id = v.partition_id)
end
else if @marker is not null
begin
insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from dbo.MSmerge_contents mc
JOIN ' + @partition_view_name + ' v
ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
and mc.rowguid = v.' + @rgcol + '
and mc.marker = @marker
where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
and mcpm.tablenick = mc.tablenick
and mcpm.rowguid = mc.rowguid
and mcpm.partition_id = v.partition_id)
end
else
begin
if @rowguid is null
insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from dbo.MSmerge_contents mc
JOIN ' + @partition_view_name + ' v
ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
and mc.rowguid = v.' + @rgcol + '
where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
and mcpm.tablenick = mc.tablenick
and mcpm.rowguid = mc.rowguid
and mcpm.partition_id = v.partition_id)
else
insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
select distinct ' + convert(nvarchar, @publication_number) + ', ' + convert(nvarchar(11), @article_nickname) + ', @rowguid, v.partition_id
from ' + @partition_view_name + ' v
where v.' + @rgcol + ' = @rowguid
and not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
and mcpm.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
and mcpm.rowguid = @rowguid
and mcpm.partition_id = v.partition_id)
end
end'
exec @retcode = sys.xp_execresultset @cmd, @dbname
if @@ERROR<>0 OR @retcode <>0 return (1)
exec @retcode = dbo.sp_MS_marksystemobject @membership_eval_proc_name
if @@ERROR<>0 OR @retcode <>0 return (1)
exec ('grant exec on dbo.' + @quoted_membership_eval_proc_name + ' to public')
if @@ERROR<>0 return (1)
update dbo.sysmergepartitioninfo set membership_eval_proc_name = @membership_eval_proc_name
where artid = @artid and pubid = @pubid
end