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