Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelpmergearticles_nonpartgroups

  No additional text.


Syntax
create procedure sys.sp_MShelpmergearticles_nonpartgroups
    @publication sysname,
    @compatibility_level int = 7000000,  -- backward compatibility level, default=Sphinx
    @pubidin uniqueidentifier = NULL
as
    declare @pubid                  uniqueidentifier
    declare @artid                  uniqueidentifier
    declare @user_name              sysname
    declare @guid_col               sysname
    declare @identity_support       int
    declare @nickname               int
    declare @next_seed              bigint
    declare @objid                  int
    declare @qualname               nvarchar(270)
    declare @retcode                int
    declare @tablename              sysname
    declare @range                  bigint
    declare @threshold              int
    declare @db_name                sysname
    declare @has_joins              int
    declare @article_filter_category int
    declare @haspartfilters         int
    declare @grouppartfilterarticles int
    declare @injoinfilters          int
    declare @nofilters              int
    declare @objid_looper           int
    declare @indexcol               int
    declare @rowcount1              int
    declare @rowcount2              int
    declare @use_partition_groups   smallint
    declare @maxschemaguidforarticle uniqueidentifier
    declare @tmp_table TABLE (tablename sysname, user_name sysname, guid_col sysname NULL, next_seed bigint NULL,
                                artid uniqueidentifier, pubid uniqueidentifier,
                                has_joins int, article_filter_category int, objid int,
                                has_relation_with_joinarticles int default 0, node_visited bit default 0,
                                permissions_bitmask int default 0, maxschemaguidforarticle uniqueidentifier NULL,
                                unique (artid, pubid), unique (objid, pubid))

    declare @worktable TABLE (objid int NOT NULL, indexcol int)

    set @nofilters = 1
    set @haspartfilters = 2
    set @injoinfilters = 4
    set @grouppartfilterarticles = 8

    /*
    ** To public.
    */
    set nocount on
    if (@publication is null)
    begin
        RAISERROR(14003, 16, -1)
        return (1)
    end

    if object_id('MSmerge_contents') is NULL
    begin
        raiserror(20054, 16, -1)
        return (1)
    end

    select @db_name = db_name()

    if @pubidin is not NULL
        set @pubid = @pubidin
    else
        select @pubid = pubid from dbo.sysmergepublications
            where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=@db_name

    -- security check
    if (@pubid is null) or {fn ISPALUSER(@pubid)} <> 1
    begin
        RAISERROR (21423, 11, -1, @publication)
        return (1)
    end

    select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid
    if @use_partition_groups is null
        select @use_partition_groups = 0

    select TOP 1 @artid=artid, @objid=objid, @nickname=nickname, @identity_support=identity_support
    from dbo.sysmergearticles
    where pubid=@pubid
    order by nickname ASC

    while (@artid is not NULL)
    begin

        select @tablename=object_name(@objid)
        if @tablename is NULL
            goto NEXT_ARTICLE

        select @maxschemaguidforarticle = sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid)
        select @user_name=SCHEMA_NAME(schema_id) from sys.objects where object_id=@objid
        select @qualname=QUOTENAME(@user_name) + '.' + QUOTENAME(@tablename)
        select @next_seed=NULL
        if @identity_support=1
        begin
            if (sys.fn_MSmerge_islocalpubid(@pubid)=1)
            begin
                select @next_seed=max_used from dbo.MSmerge_identity_range
                    where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalsubid(subid)=1)
            end
            else
            begin
                -- This is the case of a message based subscriber acting like publisher
                select @next_seed = NULL
            end
        end

        if exists (select * from dbo.sysmergesubsetfilters where join_nickname = @nickname and (filter_type & 1) = 1)
            set @has_joins = 1
        else
            set @has_joins = 0

        --if @use_partition_groups = 0
        --begin

            -- reset for subsequent bitwise OR operations.
            set @article_filter_category = 0

            if exists (select * from dbo.sysmergesubsetfilters where pubid = @pubid and (filter_type & 1) = 1 and (join_nickname = @nickname or art_nickname = @nickname))
                set @article_filter_category = (@article_filter_category | @injoinfilters)

            if exists (select * from dbo.sysmergearticles where pubid = @pubid and nickname = @nickname and datalength(subset_filterclause) > 1)
                set @article_filter_category = (@article_filter_category | @haspartfilters | @grouppartfilterarticles)

            if (@article_filter_category = 0)
                set @article_filter_category = @nofilters
        --end

        select @guid_col=name from sys.columns where object_id=@objid and is_rowguidcol=1

        if @use_partition_groups <= 0
            insert into @tmp_table values(@tablename, @user_name, @guid_col, @next_seed,
                                        @artid, @pubid, @has_joins, @article_filter_category, @objid, 0, 0,
                                        permissions(@objid), @maxschemaguidforarticle)
        else
            insert into @tmp_table select @tablename, @user_name, @guid_col, @next_seed,
                                        @artid, @pubid, @has_joins, @article_filter_category, @objid,
                                        case when (@article_filter_category & @injoinfilters) = @injoinfilters then 1 else 0 end,
                                        0, permissions(@objid), @maxschemaguidforarticle

        if @@ERROR<>0
        begin
            raiserror(21197, 16, -1)
            return (1)
        end

NEXT_ARTICLE:
        select @artid = NULL
        select TOP 1 @artid=artid, @objid=objid, @nickname=nickname, @identity_support=identity_support
        from dbo.sysmergearticles
        where pubid=@pubid
        and nickname>@nickname
        order by nickname ASC
    end

    -- Find articles that have no relations (in either direction - referring or referenced) with any articles in the join filter category.

    if @use_partition_groups <= 0
    begin
        if exists (select * from @tmp_table where (article_filter_category & @injoinfilters) = @injoinfilters)
        begin

            --create unique index #uncworktable on @worktable(objid, indexcol)
            update @tmp_table set node_visited = 1
            where objid not in (select parent_object_id from sys.foreign_keys)
            and objid not in (select referenced_object_id from sys.foreign_keys)
            and (article_filter_category & @injoinfilters) = 0

            -- get the min objid from the temp table of all articles
            select @objid_looper = min(objid) from @tmp_table where node_visited = 0

            while (@objid_looper is not null)
            begin
                delete from @worktable

                select @indexcol = 0

                insert into @worktable values (@objid_looper, @indexcol)

                -- find all the objects referenced by this object and all objects that reference this object.
                insert into @worktable select distinct referenced_object_id, @indexcol+1 from sys.foreign_keys where parent_object_id = @objid_looper
           and referenced_object_id not in (select objid from @worktable)
                select @rowcount1 = @@rowcount

                insert into @worktable select distinct parent_object_id, @indexcol+1 from sys.foreign_keys where referenced_object_id = @objid_looper
                                                and parent_object_id not in (select objid from @worktable)
                select @rowcount2 = @@rowcount

                while (@rowcount1 <> 0 or @rowcount2 <> 0)
                begin

                    select @indexcol = @indexcol+1

                    insert into @worktable select distinct s.referenced_object_id, @indexcol+1 from sys.foreign_keys s, @worktable w, @tmp_table t where s.parent_object_id = w.objid and w.objid = t.objid
                                                and w.indexcol = @indexcol and t.node_visited = 0 and s.referenced_object_id not in (select objid from @worktable)
                    select @rowcount1 = @@rowcount

                    insert into @worktable select distinct s.parent_object_id, @indexcol+1 from sys.foreign_keys s, @worktable w, @tmp_table t where s.referenced_object_id = w.objid and w.objid = t.objid
                                                and w.indexcol = @indexcol and t.node_visited = 0 and s.parent_object_id not in (select objid from @worktable)
                    select @rowcount2 = @@rowcount
                end

                if exists (select * from @worktable w, @tmp_table t where w.objid = t.objid and (t.article_filter_category & @injoinfilters) = @injoinfilters)
                begin
                    -- all articles in @worktable have a direct or indirect relation with at least one object in the join articles category.
                    update @tmp_table set node_visited = 1, has_relation_with_joinarticles = 1
                    from @tmp_table t, @worktable w
                    where w.objid = t.objid
                end
                else
                begin
                    -- none of the articles in @worktable has any relation (direct or indirect) with any article in the join articles category.
                    update @tmp_table set node_visited = 1, has_relation_with_joinarticles = 0
                    from @tmp_table t, @worktable w
                    where w.objid = t.objid
                end

                -- process more unvisited articles from @tmp_table
                select @objid_looper = min(objid) from @tmp_table where objid > @objid_looper and node_visited = 0
            end
        end
    end

    /* If the 7.0 merge agent is making this call then we need to make sure that the CLSID of the sp resolver is the old one and not the new one
       for an 8.0 merge agent we map CLSIDs from all resolvers it has in common with 9.0
       all this is done by calling fn_MSrepl_map_resolver_clsid */

    if @compatibility_level >= 9000000
    begin
        select name, t.tablename, t.user_name, a.artid, pre_creation_command, a.pubid, nickname,
            column_tracking, status,
            sys.fn_MSrepl_map_resolver_clsid(@compatibility_level, article_resolver, resolver_clsid) as resolver_clsid,
            conflict_script, conflict_table,
            insert_proc, update_proc, select_proc, destination_object, missing_col_count,
            missing_cols, t.guid_col,
            article_resolver, resolver_info, subset_filterclause, has_joins, excluded_col_count,
            excluded_cols, destination_owner, identity_support, t.next_seed, a.range, a.threshold,
            verify_resolver_signature, allow_interactive_resolver, fast_multicol_updateproc, check_permissions,
            t.article_filter_category, t.has_relation_with_joinarticles, published_in_tran_pub, aw.logical_record_parent_nickname,
            aw.logical_record_level_conflict_detection, aw.logical_record_level_conflict_resolution,
            aw.partition_options, processing_order, a.upload_options, t.permissions_bitmask, a.delete_tracking, a.compensate_for_errors,
            a.pub_range, t.maxschemaguidforarticle, a.stream_blob_columns, a.preserve_rowguidcol, a.schema_option
            from dbo.sysmergearticles a
                inner join dbo.sysmergepartitioninfo aw on a.artid = aw.artid and a.pubid = aw.pubid
                inner join @tmp_table t on a.pubid=t.pubid and a.artid=t.artid
            order by a.processing_order, a.nickname
    end
    else
    begin
        select name, t.tablename, t.user_name, a.artid, pre_creation_command, a.pubid, nickname,
            column_tracking, status,
            sys.fn_MSrepl_map_resolver_clsid(@compatibility_level, article_resolver, resolver_clsid) as resolver_clsid,
            conflict_script, conflict_table,
            'sp_ins_' + sys.fn_MSmerge_getartprocsuffix(a.artid,a.pubid) as insert_proc,
            'sp_upd_' + sys.fn_MSmerge_getartprocsuffix(a.artid,a.pubid) as update_proc,
            'sp_sel_' + sys.fn_MSmerge_getartprocsuffix(a.artid,a.pubid) as select_proc,
            destination_object, missing_col_count,
            missing_cols, t.guid_col,
            article_resolver, resolver_info, subset_filterclause, has_joins, excluded_col_count,
            excluded_cols, destination_owner, identity_support, t.next_seed, a.range, a.threshold,
            verify_resolver_signature, allow_interactive_resolver, fast_multicol_updateproc, check_permissions,
            t.article_filter_category, t.has_relation_with_joinarticles, published_in_tran_pub, a.compensate_for_errors,
            a.schema_option
            from dbo.sysmergearticles a
                inner join dbo.sysmergepartitioninfo aw on a.artid = aw.artid and a.pubid = aw.pubid
                inner join @tmp_table t on a.pubid=t.pubid and a.artid=t.artid
            order by a.processing_order, a.nickname
    end
    return (0)

 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MShelpmergearticles (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