Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

-- Requires Certificate signature for catalog access
create procedure sys.sp_MSevaluate_logicalrecordparent_allcontentsrows @pubid uniqueidentifier = NULL, @artid uniqueidentifier = NULL
    declare @in_tran bit
    select @in_tran = 0
    -- If this publication has no logical records defined, return without doing anything
    if not exists (select * from dbo.sysmergepartitioninfo where (pubid = @pubid or artid = @artid)
                    and logical_record_view is not null)
        return 0

    declare articles_in_logical_records cursor local fast_forward for
        select distinct sma.nickname, sma.objid, 'dbo.' + quotename(object_name(smaw.logical_record_view)), smaw.logical_record_parent_nickname
        from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
        where smaw.logical_record_view is not null
        and (sma.pubid = @pubid or sma.artid = @artid)
        and sma.artid = smaw.artid
        and sma.pubid = smaw.pubid
        and smaw.logical_record_parent_nickname is not null
        order by nickname
        for read only

    declare @nickname int,
            @logical_record_view_name nvarchar(270),
            @logical_record_parent_nickname int,
            @logical_record_parent_gencur bigint,
            @logical_record_parent_oldmaxversion int,
            @objid int,
            @rgcol nvarchar(270),
            @command nvarchar(max),
            @replnick binary(6),
            @retcode int,
            @dbname nvarchar(258)

    set @retcode = 1
    select @dbname = quotename(db_name())

    exec sys.sp_MSgetreplnick @replnick = @replnick output

    open articles_in_logical_records

    fetch next from articles_in_logical_records into @nickname, @objid, @logical_record_view_name, @logical_record_parent_nickname

    while @@fetch_status <> -1
        select @rgcol = quotename(name) from sys.columns where object_id = @objid and is_rowguidcol = 1
        select top 1 @logical_record_parent_oldmaxversion = maxversion_at_cleanup
        from dbo.sysmergearticles
        where nickname = @logical_record_parent_nickname

        exec  sys.sp_MSmerge_getgencur @logical_record_parent_nickname, 1, @logical_record_parent_gencur output

        select @command = N'update ' + @dbname + '.[dbo].[MSmerge_contents]
        set logical_record_parent_rowguid = lrv.logical_record_parent_rowguid
        from ' + @dbname + '.[dbo].[MSmerge_contents] mc, ' + @logical_record_view_name + ' lrv
        where mc.tablenick = @nickname
        and mc.rowguid = lrv.' + @rgcol + '

        declare @logical_record_parent_rowguids table (logical_record_parent_rowguid uniqueidentifier)
        declare @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311)

        insert into @logical_record_parent_rowguids select distinct logical_record_parent_rowguid
        from ' + @dbname + '.[dbo].[MSmerge_contents]
        where tablenick = @nickname
        and logical_record_parent_rowguid is not null

        update ' + @dbname + '.[dbo].[MSmerge_contents]
        set logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @logical_record_parent_oldmaxversion+1) }
        where tablenick = @logical_record_parent_nickname
        and rowguid in (select logical_record_parent_rowguid from @logical_record_parent_rowguids)

        delete from @logical_record_parent_rowguids
        where logical_record_parent_rowguid in
                (select rowguid from ' + @dbname + '.[dbo].[MSmerge_contents]
                    where tablenick = @logical_record_parent_nickname)

        -- Now @logical_record_parent_rowguids is left with parent rowguids that are not present in MSmerge_contents.
        if exists (select * from @logical_record_parent_rowguids)
            select @logical_record_lineage = { fn UPDATELINEAGE(0x0, @replnick, @logical_record_parent_oldmaxversion+1) }

            -- if no cleanup done yet, use 1 as the version.
            if @logical_record_parent_oldmaxversion = 1
                select @logical_record_parent_regular_lineage = { fn UPDATELINEAGE(0x0, @replnick, 1) }
                select @logical_record_parent_regular_lineage = @logical_record_lineage

            insert into ' + @dbname + '.[dbo].[MSmerge_contents] (tablenick, rowguid, lineage, colv1, generation, partchangegen,
                logical_record_parent_rowguid, logical_record_lineage)
            select distinct @logical_record_parent_nickname, lrpg.logical_record_parent_rowguid,
                @logical_record_parent_regular_lineage, 0x00, @logical_record_parent_gencur, NULL, lrpg.logical_record_parent_rowguid,
            from @logical_record_parent_rowguids lrpg
            where not exists (select rowguid from ' + @dbname + '.[dbo].[MSmerge_contents] where tablenick = @logical_record_parent_nickname
                                and rowguid = lrpg.logical_record_parent_rowguid)
        end '

        begin tran
        save tran eval_logical_record_parent
        select @in_tran = 1

        exec sys.sp_executesql @command,
            N'@nickname int, @logical_record_parent_nickname int, @logical_record_parent_gencur bigint,
            @logical_record_parent_oldmaxversion int, @replnick binary(6)',
        if @@error <> 0
            goto EXIT_PROC

        commit tran
        select @in_tran = 0

        fetch next from articles_in_logical_records into @nickname, @objid, @logical_record_view_name, @logical_record_parent_nickname

    select @retcode = 0


if @in_tran = 1
    rollback tran eval_logical_record_parent
    commit tran
    select @in_tran = 0

close articles_in_logical_records
deallocate articles_in_logical_records

return @retcode

Last revision 2008RTM
See also

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