Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSgetmetadatabatch

  No additional text.


Syntax
create procedure sys.sp_MSgetmetadatabatch
    (@pubid uniqueidentifier,
     @tablenickarray varbinary(2000),
     @rowguidarray varbinary(8000),
     @compatlevel int = 10,             -- backward compatibility level, default=Sphinx
     @lightweight int = 0)              -- if <>0, return some stuff needed to cope with lightweight subscriber
as
    declare @tablenick          int
    declare @tablenicklast      int
    declare @rowguid            uniqueidentifier
    declare @generation         bigint
    declare @type                       tinyint
    declare @lineage            varbinary(311)
    declare @colv                       varbinary(2953)
    declare @retcode        smallint
    declare @tnlength       int
    declare @tnoffset       int
    declare @guidoffset     int
    declare @procname       nvarchar(270)
    declare @iscoltracked   int
    declare @maxversion         int
    declare @cCols int

    -- create temp table for returning results
    declare @meta_batch TABLE (idx int identity unique, generation bigint, type tinyint,
        lineage varbinary(311), colv varbinary(2953), maxversion int)

    -- security check
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid=@pubid
    if @@error <> 0 or @retcode <> 0
            return 1

    set @tablenicklast = 0
    -- initialize offsets and length for walking through arrays
    set @tnoffset = 1
    set @guidoffset = 1
    set @tnlength = datalength(@tablenickarray)


    -- walk through arrays and populate temp table
    while (@tnoffset < @tnlength)
    begin
        set @tablenick = substring(@tablenickarray, @tnoffset, 4)
        set @rowguid = substring(@rowguidarray, @guidoffset, 16)

        -- instead of calling sp_MSgetrowmetadata, look it up ourselves might be faster

        --  exec @rc = sp_MSgetrowmetadata @tablenick, @rowguid,  @generation output,
        --  @type  output, @lineage output, @colv output, @pubid
        if @tablenick <> @tablenicklast
            begin
            select @procname= 'dbo.' + select_proc, @iscoltracked= column_tracking from dbo.sysmergearticles where nickname = @tablenick and pubid=@pubid
            set @tablenicklast = @tablenick
            end

        set @type= 4
        set @generation= 0
        set @lineage= NULL
        set @colv= NULL

        -- check for row in base table
        exec @retcode= @procname @maxschemaguidforarticle = NULL, @type=@type output, @rowguid=@rowguid
        if @@error <>0 or @retcode <> 0
        begin
            return (1)
        end

        select @cCols = 0
        if (@type = 3)
        begin
            if @compatlevel < 90
            begin
                set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)
            end

            -- here do a union query between contents and tombstone
            -- The normal case here would be: Either the row is in contents or if the row
            -- was added as part of the initial snapshot it does not have a contents row
            -- however, in the exception case, if there is some partition movement or some
            -- deletes happening while we do this query, the row could have been deleted
            -- and the MSmerge_contents entry for the row will not be present.
            -- To cover the exception case we do a union query here between contents and tombstones.
            select @type = type, @generation = generation, @lineage = lineage, @colv = colv from
            (
                select 2 as type,  generation as generation,
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,
                       case when @compatlevel >= 90 or @iscoltracked = 0 then colv1 else {fn COLV_90_TO_80(colv1, @cCols)} end as colv
                    from dbo.MSmerge_contents
                    where tablenick = @tablenick and rowguid = @rowguid
                union
                select type as type, generation as generation,
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,
                       NULL as colv
                    from dbo.MSmerge_tombstone
                    where tablenick = @tablenick and rowguid = @rowguid
            ) as metadataunion

        end
        else
        begin
            set @type= 0

            if @compatlevel < 90
            begin
                set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)
            end

            -- here do a union query between contents and tombstone
            -- The normal case here would be: Either the row is in tombstones or if the row
            -- was never present it will have no entry in tombstone.
            -- however, in the exception case, if there is some partition movement or some
            -- re-inserts happening while we do this query, the row could have been re-inserted
            -- and the tombstone entry will not be present.
            -- To cover the exception case we do a union query here between contents and tombstones.
            -- which should be done as an atomic operation.
            select @type = type, @generation = generation, @lineage = lineage, @colv = colv from
            (
                select 2 as type,  generation as generation,
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,
                       case when @compatlevel >= 90 or @iscoltracked = 0 then colv1 else {fn COLV_90_TO_80(colv1, @cCols)} end as colv
                    from dbo.MSmerge_contents
                    where tablenick = @tablenick and rowguid = @rowguid
                union
                select type as type, generation as generation,
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,
                       NULL as colv
                    from dbo.MSmerge_tombstone
                    where tablenick = @tablenick and rowguid = @rowguid
            ) as metadataunion2

        end


        -- insert values into temp table
        if @lightweight <> 0
        begin
            select @maxversion= maxversion_at_cleanup from dbo.sysmergearticles
                    where nickname = @tablenick and pubid=@pubid

            insert into @meta_batch (generation, type, lineage, colv, maxversion) values
                (@generation, @type, @lineage, @colv, @maxversion)
        end
        else
        begin
            insert into @meta_batch (generation, type, lineage, colv) values
                (@generation, @type, @lineage, @colv)
        end

        -- bump up offsets for next time through loop
        set @tnoffset = @tnoffset + 4
        set @guidoffset = @guidoffset + 16
    end

    -- select out our result set
    if @lightweight <> 0
    begin
        select generation, type, lineage, colv, maxversion from @meta_batch order by idx
    end
    else
    begin
        select generation, type, lineage, colv from @meta_batch order by idx
    end

    return (0)

 
Last revision 2008RTM
See also

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