Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MSdetermine_logical_record_parents @pubid uniqueidentifier
    set nocount on
    declare @retcode int, @publication sysname

    -- Return immediately if no logical record relationships are defined.
    if not exists (select * from dbo.sysmergesubsetfilters
                    where (filter_type & 2) = 2
                    and pubid = @pubid)
        return 0

    -- Now we know this publication has one or more logical record relationships.
    -- Make sure partition groups are in use (check only at publisher). Logical records are available only with
    -- partition groups.
    exec @retcode = sys.sp_MScheckatpublisher @pubid    -- @retcode = 0 implies "at publisher".
    if @retcode = 0 and exists
                    (select * from dbo.sysmergepublications where pubid = @pubid
                        and use_partition_groups <= 0
                        and (pubid in
                                (select pubid from dbo.sysmergearticles
                                where len(subset_filterclause) > 0)
                            or pubid in
                                (select pubid from dbo.sysmergesubsetfilters
                                where (filter_type & 1) = 1)))

        select @publication = name from dbo.sysmergepublications where pubid = @pubid
        raiserror(21571, 16, -1, @publication)
        return 1

    declare @logical_record_articles table (nickname int, parent_nickname int null)
    declare @top_level_articles table (nickname int)
    declare @worktable table (nickname int)

    -- First find the ones that are top-level parents.
    -- The following query does not look at pubid because the logical record parent of a given article
    -- has to be the same for all publications. e.g. if publication 1 has logical record
    -- A1->A2->A3 and publication 2 has A2->A3->A4, it would appear that publication 1 has
    -- A1 as the parent and publication 2 has A2 as the parent. But in reality, both have to have
    -- A1 as the parent. Otherwise, in one publication A2, A3 would have A1 as the parent and in
    -- the other A2, A3 would have A2 as the parent. This would create problems in the tracking since
    -- contents rows are not on a per-publication basis. This obviously imposes limitations on the choice
    -- of logical record definitions, but we will assume that the typical usage of this feature would
    -- be to have either identical or non-overlapping logical record relationships in different publications.

    -- Top-level parents are considered to have themselves as their parents.
    -- Save off such articles in @top_level_articles
    insert into @top_level_articles (nickname)
        select distinct join_nickname from dbo.sysmergesubsetfilters
            where (filter_type & 2) = 2
            and join_nickname not in (select art_nickname from dbo.sysmergesubsetfilters
                                        where (filter_type & 2) = 2)

    insert into @logical_record_articles (nickname)
        select distinct nickname from @top_level_articles

    insert into @logical_record_articles (nickname)
        select distinct art_nickname from dbo.sysmergesubsetfilters
            where (filter_type & 2) = 2
            and art_nickname not in (select nickname from @logical_record_articles)

    -- For each nickname in @logical_record_articles which still has parent_nickname = NULL in @logical_record_articles,
    -- find the top-level parent.
    while exists (select * from @logical_record_articles where parent_nickname is NULL)
        declare @keep_traversing_upwards bit

        -- get one article - start with the one with min nickname
        insert into @worktable (nickname)
            select min(nickname) from @logical_record_articles
                where parent_nickname is NULL

        set @keep_traversing_upwards = 1

        while @keep_traversing_upwards = 1
            -- work your way up until you find the top-level parent.
            insert into @worktable(nickname)
                select join_nickname from dbo.sysmergesubsetfilters
                where art_nickname in (select nickname from @worktable)
                and join_nickname not in (select nickname from @worktable)
                and (filter_type & 2) = 2

            if @@rowcount <> 0
                set @keep_traversing_upwards = 1
                set @keep_traversing_upwards = 0

        update @logical_record_articles
            set parent_nickname =
                (select top 1 nickname from @top_level_articles
                    where nickname in (select nickname from @worktable))
        where nickname in (select nickname from @worktable)

        delete from @worktable

    update dbo.sysmergepartitioninfo set logical_record_parent_nickname = parent_nickname
    from dbo.sysmergepartitioninfo smaw, @logical_record_articles lra
    where smaw.artid in (select sma.artid from dbo.sysmergearticles sma where sma.nickname = lra.nickname)
    and smaw.pubid in (select pubid from dbo.sysmergesubsetfilters smsf where
						(art_nickname = lra.nickname or join_nickname = lra.nickname)
						and (filter_type & 2) = 2)

    return 0

Last revision 2008RTM
See also

  sp_MScreate_logical_record_views (Procedure)
sp_MSdrop_rladmin (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash