Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSsetup_publication_for_partition_groups

  No additional text.


Syntax
create procedure sys.sp_MSsetup_publication_for_partition_groups (@pubid uniqueidentifier) as
begin
    declare @article_name sysname, @article_nickname int, @retcode int, @pubname sysname,
            @expand_proc sysname, @cmd nvarchar(max), @dbname sysname,    @guidstr nvarchar(32),
            @artid uniqueidentifier, @art_index int, @article_level int, @progress int

    declare @articles table (indexcol int identity NOT NULL, art_nick int NOT NULL, artid uniqueidentifier, article_level int NOT NULL)

    select @dbname = db_name()

    select @pubname = name from dbo.sysmergepublications where pubid = @pubid

    --remake the publication views
    --exec @retcode = sys.sp_MSpublicationview @pubname, 1
    --if @@error <> 0 or @retcode <> 0
    --begin
    --    return 1
    --end

    set @progress       = 1
    set @article_level  = 0

    -- We need to generate expand procs in reverse order of parent-child relationships, i.e. child first, then
    -- parent. This is because a child needn't know of the existence of parent's expand proc to do its expansion,
    -- but the parent needs to know of the child's expand proc to do its expansion.
    while @progress > 0
    BEGIN
        /*
        ** Select articles that have either a boolean_filter or at least one join filter
        ** into a temp table in an optimized order.
        */
        -- This inserts into @articles the article levels of all articles.
        insert into @articles(art_nick, artid, article_level) select nickname, artid, @article_level from dbo.sysmergearticles
            where pubid=@pubid and nickname not in (select art_nick from @articles)
                and nickname not in
                (select  art_nickname from dbo.sysmergesubsetfilters
                    where pubid=@pubid and join_nickname not in
                        (select art_nick from @articles))

        set @progress = @@rowcount
        select @article_level = @article_level + 1
    END

    select @art_index = max(indexcol) from @articles

    while (@art_index is not null)
    begin
        select @article_nickname = art_nick, @artid = artid, @article_level = article_level from @articles
                where indexcol = @art_index

        set @guidstr = NULL
        set @expand_proc = NULL

        exec @retcode=sys.sp_MSguidtostr @artid, @guidstr out
        if @retcode<>0 or @@ERROR<>0
            return (1)

        exec @retcode = sp_MSgenerate_articlechangemembershipevaluation_proc @pubid, @article_nickname
        if @@error <> 0 or @retcode <> 0
        begin
            return 1
        end

        if exists (select * from dbo.sysmergepartitioninfo where partition_view_id is not null and artid = @artid)
        begin
            select top 1 @expand_proc = expand_proc from dbo.sysmergepartitioninfo
            where artid = @artid
            and expand_proc is not null

            if @expand_proc is not null
            begin
                select @cmd = 'if object_id(''dbo.' + replace(quotename(@expand_proc), '''', '''''') + ''') is not null drop procedure dbo.' + quotename(@expand_proc)
                exec (@cmd)
                if @@error <> 0
                    return 1
            end
            else
            begin
                select @expand_proc = 'MSmerge_expand_sp_' + @guidstr
            end

            if exists (select * from dbo.sysmergesubsetfilters where join_nickname = @article_nickname and (filter_type & 1) = 1)
            begin
                select @cmd = 'exec sp_MSgenerateexpandproc ' + convert(nvarchar, @article_nickname) + ', ' + quotename(@expand_proc)
                exec @retcode = sys.xp_execresultset @cmd, @dbname
                if @@ERROR<>0 OR @retcode<>0
                begin
                    return (1)
                end

                exec dbo.sp_MS_marksystemobject @expand_proc

			   	select @cmd = 'grant execute on dbo.' + quotename(@expand_proc) + ' to public'
                exec (@cmd)

                update dbo.sysmergepartitioninfo set expand_proc = @expand_proc
                where artid = @artid
            end
        end

        select @art_index = max(indexcol) from @articles where indexcol < @art_index
    end

    /*
    exec @retcode = sys.sp_MSregenerate_mergetriggers @pubname
    if @@ERROR<>0 OR @retcode<>0
    begin
        return (1)
    end

    select @art_index = min(indexcol) from @articles

    while (@art_index is not null)
    begin
        select @artid = artid from @articles where indexcol = @art_index

        exec @retcode = sys.sp_MScreate_article_repl_view @pubid, @artid
        if @@ERROR<>0 OR @retcode<>0
        begin
            return (1)
        end

        select @art_index = min(indexcol) from @articles where indexcol > @art_index
    end
    */

    exec @retcode = sys.sp_MSevaluate_change_membership_for_all_articles_in_pubid @pubid=@pubid
    if @@ERROR<>0 OR @retcode<>0
    begin
        return (1)
    end

    exec @retcode = sys.sp_MSevaluate_logicalrecordparent_allcontentsrows @pubid=@pubid
    if @@ERROR<>0 OR @retcode<>0
    begin
        return (1)
    end

    return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSsetup_partition_groups (Procedure)
sp_vupgrade_mergeobjects (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