Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdisable_use_partition_groups

  No additional text.


Syntax


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

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSsetup_use_partition_groups (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash