Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSsetupworktables

  No additional text.


Syntax

-- Modify temp table. No security check needed.
create procedure sys.sp_MSsetupworktables
    @pubid              uniqueidentifier,
    @genlist            varchar(8000),
    @articlesoption     int,    -- 0=process all articles, 1=process this specific article (whose nickname is passed in @tablenickname), 2=all articles involved in join filters, 3=process all articles involved in part filters, 4=process articles whose nic
knames have been passed in @nicknamelist.
    @tablenickname      int,
    @nicknamelist       varchar(8000),
    @mingen             bigint = 0,
    @maxgen             bigint = 0,
    @skipgenlist        varchar(8000) = NULL,
    @contents_subset_rowcount int OUTPUT,
    @tombstone_subset_rowcount int OUTPUT
AS

    declare @lengenlist int
    declare @lenskipgenlist int

    -- put in a nickname with value 0 - This will match
    -- gen history rows put in by downlevel subscribers
    -- with art_nick = NULL
    insert into #nicknames_to_process values (0)

    if (@articlesoption = 0)
    begin

        -- process all articles for this publication
        insert into #nicknames_to_process
        select distinct nickname
        from dbo.sysmergearticles a
        where pubid = @pubid

    end
    else if (@articlesoption = 1)
    begin

        --process only the article whose nickname has been passed-in
        insert into #nicknames_to_process values (@tablenickname)

    end
    else if (@articlesoption = 2)
    begin
        --process all articles with join filters (article could be on any side - left or right - of any join filter)
        insert into #nicknames_to_process
        select distinct nickname
        from dbo.sysmergearticles a
        where pubid = @pubid
        and exists (select * from dbo.sysmergesubsetfilters s
                    where s.pubid = @pubid and (s.filter_type & 1) = 1
                    and (s.art_nickname = a.nickname or s.join_nickname = a.nickname))
    end
    else if (@articlesoption = 3)
    begin

        --process all articles with part filters but that don't have a join filter.
        insert into #nicknames_to_process
        select distinct nickname
        from dbo.sysmergearticles a
        where pubid = @pubid
        and datalength(subset_filterclause) > 1
        and not exists (select * from dbo.sysmergesubsetfilters s
                    where s.pubid = @pubid  and (s.filter_type & 1) = 1
                    and (s.art_nickname = a.nickname or s.join_nickname = a.nickname))
    end
    else if (@articlesoption = 4)
    begin
        --process all articles whose nicknames have been passed into @nicknamelist
        if (@nicknamelist is not null and rtrim(ltrim(@nicknamelist)) <> '')
        begin
            exec ('insert into #nicknames_to_process select distinct nickname from dbo.sysmergearticles where nickname in (' + @nicknamelist + ')')
            if @@ERROR <>0 return (1)
        end
    end

    -- Create index on #nicknames_to_process now that it has been populated. Creating it after data insertion is better because
    -- that generates the stats for the index. That helps in the insert into #contents_subset query. The other option was to
    -- create the index, insert the data, and then update statistics.
    create unique index #nicknames_ind on #nicknames_to_process (nickname)

    if (@maxgen <> 0)
    begin

        insert into #genlist select distinct generation
            from dbo.MSmerge_genhistory gh
            join #nicknames_to_process np
            on isnull(gh.art_nick,0) = np.nickname and
            gh.generation >= @mingen
            and gh.generation <= @maxgen

        select @lengenlist = isnull(datalength(@genlist),0)
        select @lenskipgenlist = isnull(datalength(@skipgenlist),0)

        -- no need to do ltrim and rtrim on the @skipgenlist. sp_MSsetupbelongs already did that.
        if (@skipgenlist is not null and @skipgenlist <> '' and @lenskipgenlist <= @lengenlist)
        begin
            exec('delete from #genlist where generation in (' + @skipgenlist + ')')
        end
        else if (@genlist is not null and @genlist <> '')
        begin
            -- gen 0 won't be in @genlist, so will get deleted because of the NOT IN.
            exec('delete from #genlist where generation not in (' + @genlist + ')')
        end
    end
    else if (@genlist is not null and @genlist <> '')
    begin
        exec ('insert into #genlist select distinct generation from dbo.MSmerge_genhistory where
            (isnull(art_nick,0) in (select nickname from #nicknames_to_process)) and
            generation in (' + @genlist + ') ')
        if @@ERROR <>0 return (1)
    end

    -- Create index on #genlist now that it has been populated. Creating it after data insertion is better because
    -- that generates the stats for the index. That helps in the insert into #contents_subset query. The other option was to
    -- create the index, insert the data, and then update statistics.
    create unique index #genlist_ind on #genlist (generation)

    if @maxgen is null
        select @maxgen = 0

    if @mingen is null
        select @mingen = 0

    if (@maxgen = 0)
    begin
        -- SQL 7.0 pull merge agents do not pass in @maxgen and @mingen, so the default is 0.
        -- For them need to compute min and max. Note that we could do the same for 8.0 agents as well
        -- but 8.0 agents pass the min and max because they are used in the INSERT INTO #genlist query above
        -- when we don't have the #genlist table and all we have is the comma-separated @genlist string.
        select @mingen = min(generation), @maxgen = max(generation) from #genlist
    end

    insert into #contents_subset (tablenick, rowguid, generation, partchangegen)
    select c.tablenick, c.rowguid, c.generation, c.partchangegen
    from dbo.MSmerge_contents c
    JOIN #nicknames_to_process a
    ON c.generation >= @mingen
    AND c.generation <= @maxgen
    AND c.tablenick = a.nickname
    JOIN #genlist g
    ON c.generation = g.generation
    AND c.generation >= @mingen
    AND c.generation <= @maxgen

    UNION

    -- the rows retrieved below have a partition change that falls into the relevant generation range
    -- if we do not process them now, we would later on assume that the partition change has already been processed
    select c.tablenick, c.rowguid, c.generation, c.partchangegen
    from dbo.MSmerge_contents c
    JOIN #nicknames_to_process a
    ON c.partchangegen >= @mingen
    AND c.partchangegen <= @maxgen
    AND c.tablenick = a.nickname
    JOIN #genlist g
    ON c.partchangegen = g.generation
    AND c.partchangegen >= @mingen
    AND c.partchangegen <= @maxgen

    select @contents_subset_rowcount = @@rowcount

    insert into #tombstone_subset (tablenick, rowguid, type, generation)
    select t.tablenick, t.rowguid, t.type, t.generation
    from dbo.MSmerge_tombstone t
    JOIN #nicknames_to_process a
    ON t.generation >= @mingen
    AND t.generation <= @maxgen
    AND t.tablenick = a.nickname
    JOIN #genlist g
    ON t.generation = g.generation
    AND t.generation >= @mingen
    AND t.generation <= @maxgen

    select @tombstone_subset_rowcount = @@rowcount

    create clustered index #ucind_contents_subset on #contents_subset (tablenick, rowguid) with FILLFACTOR = 100
    create clustered index #ucind_tombstone_subset on #tombstone_subset (tablenick, rowguid) with FILLFACTOR = 100

    delete #nicknames_to_process from #nicknames_to_process ntp
    where not exists (select tablenick from #contents_subset cs where cs.tablenick = ntp.nickname)
    and not exists (select tablenick from #tombstone_subset ts where ts.tablenick = ntp.nickname)

    -- remove the nickname with value 0 that we put in specially at the beginning of this proc.
    delete from #nicknames_to_process where nickname = 0
    return (0)

 
Last revision 2008RTM
See also

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