-- Requires Certificate signature for catalog access
create procedure sys.sp_MSdisable_use_partition_groups (@pubid uniqueidentifier) as
begin
declare @pubname sysname, @artid uniqueidentifier, @partition_id_eval_proc nvarchar(264), @partition_view sysname,
@membership_eval_proc_name sysname, @expand_proc sysname, @logical_record_view nvarchar(270), @retcode int,
@quoted_name nvarchar(258)
select @pubname = name, @partition_id_eval_proc = '[dbo].' + quotename(partition_id_eval_proc)
from dbo.sysmergepublications where pubid = @pubid
-- create the ArticleChangeMembershipEvaluation proc for each article in this publication.
declare articles_in_publication CURSOR LOCAL FAST_FORWARD FOR
select artid, object_name(partition_view_id), membership_eval_proc_name, expand_proc,
object_name(logical_record_view)
from dbo.sysmergepartitioninfo
where pubid = @pubid
FOR READ ONLY
open articles_in_publication
fetch next from articles_in_publication into @artid, @partition_view, @membership_eval_proc_name,
@expand_proc, @logical_record_view
while (@@fetch_status <> -1)
begin
if @partition_view is not null and object_id(@partition_view) is not null
begin
set @quoted_name= quotename(@partition_view)
exec ('drop view ' + @quoted_name)
if @@error <> 0
return 1
end
if @membership_eval_proc_name is not null and object_id(@membership_eval_proc_name) is not null
begin
set @quoted_name= quotename(@membership_eval_proc_name)
exec ('drop procedure ' + @quoted_name)
if @@error <> 0
return 1
end
if @expand_proc is not null and object_id(@expand_proc) is not null
begin
set @quoted_name= quotename(@expand_proc)
exec ('drop procedure ' + @quoted_name)
if @@error <> 0
return 1
end
if @logical_record_view is not null and object_id(@logical_record_view) is not null
and not exists (select * from sysmergepartitioninfo where logical_record_view = @logical_record_view
and pubid <> @pubid)
begin
set @quoted_name= quotename(@logical_record_view)
exec ('drop view ' + @quoted_name)
if @@error <> 0
return 1
end
update dbo.sysmergepartitioninfo
set partition_view_id = NULL,
partition_deleted_view_rule = NULL,
partition_inserted_view_rule = NULL,
membership_eval_proc_name = NULL,
column_list = NULL,
column_list_blob = NULL,
expand_proc = NULL,
logical_record_parent_nickname = NULL,
logical_record_view = NULL,
logical_record_deleted_view_rule = NULL,
logical_record_level_conflict_detection = 0,
logical_record_level_conflict_resolution = 0
where artid = @artid and pubid = @pubid
fetch next from articles_in_publication into @artid, @partition_view, @membership_eval_proc_name,
@expand_proc, @logical_record_view
end
close articles_in_publication
deallocate articles_in_publication
-- we should not drop the parition_id_eval_proc here
-- It should be dropped when the publication gets dropped. Should not drop it here since
-- we may want to use filtering even though we don't use parition ids.
/*
if @partition_id_eval_proc is not null and object_id(@partition_id_eval_proc) is not null
begin
exec ('drop procedure ' + @partition_id_eval_proc)
if @@error <> 0
return 1
end
update dbo.sysmergepublications set partition_id_eval_proc = NULL
where pubid = @pubid
*/
exec @retcode = sp_MSregenerate_mergetriggers @pubname
if @@ERROR<>0 OR @retcode<>0
begin
return (1)
end
update dbo.sysmergepublications set use_partition_groups = 0
where pubid = @pubid and use_partition_groups = -1
return 0
end