Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSgettablecontents

  No additional text.


Syntax
create procedure sys.sp_MSgettablecontents
    @pubid uniqueidentifier,
    @dynamic_snapshot_views_table_name sysname = NULL,
    @need_ctsview_rowtrack bit = 0,
    @create_dynamic_views bit = 0,
    @max_bcp_gen bigint
as
    declare @tablenick              int
    declare @new_active             int
    declare @new_inactive           int
    declare @artname                sysname
    declare @view_name_90_forall    nvarchar(60)
    declare @view_name_90_forglobal nvarchar(60)
    declare @view_name_rowtrack     nvarchar(60)
    declare @view_name_80           nvarchar(60)
    declare @filters_view_80        nvarchar(60)
    declare @filters_view_90        nvarchar(60)
    declare @quoted_view_name_90_forall nvarchar(70)
    declare @quoted_view_name_90_forglobal nvarchar(70)
    declare @quoted_view_name_rowtrack nvarchar(70)
    declare @quoted_view_name_80    nvarchar(70)
    declare @quoted_filters_view_80 nvarchar(60)
    declare @quoted_filters_view_90 nvarchar(60)
    declare @rowguidcolname         sysname
    declare @sync_viewname          sysname
    declare @quoted_sync_viewname   sysname
    declare @objid                  int
    declare @guidstr        nvarchar(40)
    declare @retcode int
    declare @tablenickstr nvarchar(12)
    declare @view_name nvarchar(270)
    declare @select_command nvarchar(4000)
    declare @processing_order int
    declare @tempguid uniqueidentifier
    declare @artid uniqueidentifier
    declare @unique_art_name sysname
    declare @upload_options tinyint
    declare @compatlevel int
    declare @column_tracking int
    declare @colv_entries int

    set @guidstr = '''' + convert(nchar(36), @pubid) + ''''

    select @new_inactive = 5 --special article status for adding article after snapshot
    select @new_active = 6   --special article status for adding article after snapshot

    select @compatlevel = backward_comp_level from dbo.sysmergepublications where pubid = @pubid

    select Top 1 @tablenick = nickname, @processing_order = processing_order,
        @sync_viewname=object_name(sync_objid), @objid=objid, @artname=name, @artid=artid,
        @upload_options = upload_options, @column_tracking = column_tracking
    from dbo.sysmergearticles
    where pubid = @pubid
    and (status=@new_inactive or status=@new_active)
    order by processing_order desc, nickname desc
    while @objid is not null
    begin
        select @colv_entries = sys.fn_cColvEntries_80(@pubid, @tablenick)
        if @dynamic_snapshot_views_table_name is not NULL or @create_dynamic_views = 1
            select @tempguid = newid()
        else
            select @tempguid = @artid

        exec @retcode = sys.sp_MSguidtostr @tempguid, @unique_art_name out
        if @@ERROR<>0 OR @retcode<>0 return (1)

        if (@dynamic_snapshot_views_table_name is not NULL)
        begin
            select @select_command = '
                select @view_name = dsvt.dynamic_snapshot_view_name
                  from dbo.sysmergearticles sma
                inner join ' + quotename(@dynamic_snapshot_views_table_name) + ' dsvt
                    on dsvt.artid = sma.artid
                 where pubid = @pubid
                   and nickname = @tablenick'
            exec sys.sp_executesql @select_command,
                               N'@view_name nvarchar(270) output,
                                 @pubid uniqueidentifier,
                                 @tablenick int',
                               @view_name = @view_name output,
                               @pubid = @pubid,
                               @tablenick = @tablenick
            if @@error<>0 return(1)
            select @quoted_sync_viewname = quotename(@view_name)
        end
        else
        begin
            select @quoted_sync_viewname = QUOTENAME(@sync_viewname)
        end

        set @tablenickstr = convert(nchar(12), @tablenick)
        select @rowguidcolname = name from sys.columns where object_id = @objid and is_rowguidcol=1

        -- Must drop the view (if exists) and recreate
        -- We have 80 and 90 views here. Since a 90 publisher can only be run with a 90 snapshot agent
        -- the 90 snapshot agent should be able to create the incremental sysmergesubsetfilters bcp
        -- file for both 90 and 80. The 80 merge agent should be able bcp in this file based on the
        -- parameters specified in the schema text.

        -- only create contents bcp if there are contents rows
        if exists (select 1 from dbo.MSmerge_contents where tablenick = @tablenick)
        begin
            if @upload_options<>1 and @upload_options<>2
            begin
                select @view_name_90_forall='MSmerge_contents_' + @unique_art_name + '_90_forall'
                select @quoted_view_name_90_forall=QUOTENAME(@view_name_90_forall)

                if object_id(@view_name_90_forall,'V') is not NULL
                begin
                    exec ('drop view ' + @quoted_view_name_90_forall)
                end
                select @select_command = 'create view [dbo].' + @quoted_view_name_90_forall +
                    ' as select * from dbo.MSmerge_contents
                            where (tablenick = ' + @tablenickstr + ' and
                            rowguid in (select ' + quotename(@rowguidcolname) + ' from ' + @quoted_sync_viewname + ')
                    ) and generation <= ' + convert(nvarchar, @max_bcp_gen)
                exec (@select_command)
                if @@error<>0 return 1
                exec @retcode = sys.sp_MS_marksystemobject @quoted_view_name_90_forall
                if @@ERROR<>0 or @retcode<>0  return (1)

                select @view_name_90_forglobal = NULL
            end
            else
            begin
                select @view_name_90_forall = NULL

                select @view_name_90_forglobal='MSmerge_contents_' + @unique_art_name + '_90_forglobal'
                select @quoted_view_name_90_forglobal=QUOTENAME(@view_name_90_forglobal)

                if object_id(@view_name_90_forglobal,'V') is not NULL
                begin
                    exec ('drop view ' + @quoted_view_name_90_forglobal)
                end
                select @select_command = 'create view [dbo].' + @quoted_view_name_90_forglobal +
                    ' as select * from dbo.MSmerge_contents
                            where (tablenick = ' + @tablenickstr + ' and
                            rowguid in (select ' + quotename(@rowguidcolname) + ' from ' + @quoted_sync_viewname + ')
                    ) and generation <= ' + convert(nvarchar, @max_bcp_gen)
                exec (@select_command)
                if @@error<>0 return 1
                exec @retcode = sys.sp_MS_marksystemobject @quoted_view_name_90_forglobal
                if @@ERROR<>0 or @retcode<>0  return (1)
            end

            if @need_ctsview_rowtrack = 1 and @upload_options<>1 and @upload_options<>2
            begin
                select @view_name_rowtrack='MSmerge_contents_' + @unique_art_name + '_rowtrack'
                select @quoted_view_name_rowtrack=QUOTENAME(@view_name_rowtrack)

                if object_id(@view_name_rowtrack,'V') is not NULL
                begin
                    exec ('drop view ' + @quoted_view_name_rowtrack)
                end
                select @select_command = 'create view [dbo].' + @quoted_view_name_rowtrack +
                    ' as select
                            tablenick,
                            rowguid,
                            changetype=cast(11 as tinyint), -- @METADATA_TYPE_UpsertLightweightProcessed
                            changed=cast(sys.fn_MSdayasnumber(getdate()) as int),
                            rowvector=substring(lineage, 1, 10) + 0xFF,
                            changedcolumns=cast(null as varbinary),
                            columns_enumeration='
                if @column_tracking = 1
                    select @select_command = @select_command + 'cast (0 as tinyint)'
                else
                    select @select_command = @select_command + 'cast (2 as tinyint)'
                select @select_command = @select_command + ',
                        sync_cookie=null
                        from dbo.MSmerge_contents
                        where (tablenick = ' + @tablenickstr + ' and
                               rowguid in (select ' + quotename(@rowguidcolname) + ' from ' + @quoted_sync_viewname + ')
                              ) and generation <= ' + convert(nvarchar, @max_bcp_gen)
                exec (@select_command)
                if @@error<>0 return 1
                exec @retcode = sys.sp_MS_marksystemobject @quoted_view_name_rowtrack
                if @@ERROR<>0 or @retcode<>0  return (1)
            end
            else
                select @view_name_rowtrack = NULL

            if @compatlevel < 90
            begin
                select @view_name_80='MSmerge_contents_' + @unique_art_name
                select @quoted_view_name_80=QUOTENAME(@view_name_80)

                if object_id(@view_name_80,'V') is not NULL
                begin
                    exec ('drop view ' + @quoted_view_name_80)
                end

                -- need to only select out of the forall view. The reason is that upload_options
                -- cannot be 1 or 2 when the pub compat level is less than 90. Hence this view
                -- has to be there.
                select @select_command = 'create view [dbo].' + @quoted_view_name_80 + ' as
                select tablenick, rowguid,
                generation = case when abs(generation) > 2147483647 then 0 else isnull(convert(int, generation),0) end,
                partchangegen = case when abs(partchangegen) > 2147483647 then 0 else convert(int, case when partchangegen < 0 then NULL else partchangegen end) end,
                joinchangegen = case when abs(partchangegen) > 2147483647 then 0 else convert(int, case when partchangegen < 0 then (-partchangegen) else partchangegen end) end,
                lineage = {fn LINEAGE_90_TO_80(lineage)},
                colv1 = '
                if @column_tracking = 1
                    select @select_command = @select_command + '{fn COLV_90_TO_80(colv1, ' + convert(nvarchar(8), @colv_entries) + ')}'
                else
                    select @select_command = @select_command + 'cast(null as varbinary)'
                select @select_command = @select_command + '
                from ' + @quoted_view_name_90_forall

                exec (@select_command)
                if @@error<>0 return 1
                exec @retcode = sys.sp_MS_marksystemobject @quoted_view_name_80
                if @@ERROR<>0 or @retcode<>0  return (1)

            end
            else
                select @view_name_80 = NULL
        end
        else
        begin
            select @view_name_90_forall = NULL
            select @view_name_90_forglobal = NULL
            select @view_name_rowtrack = NULL
            select @view_name_80 = NULL
        end

        select @filters_view_90='sysmergesubsetfilters_' + @unique_art_name + '_90'
        select @quoted_filters_view_90=QUOTENAME(@filters_view_90)

        if object_id(@filters_view_90,'V') is not NULL
        begin
            exec ('drop view ' + @quoted_filters_view_90)
        end
        exec('create view [dbo].' + @quoted_filters_view_90 +
            ' as select filtername, join_filterid, pubid, artid, art_nickname, join_articlename,
                join_nickname, join_unique_key, expand_proc, join_filterclause, filter_type from dbo.sysmergesubsetfilters
                    where art_nickname = ' + @tablenickstr + ' and pubid = ' + @guidstr)
        if @@error<>0 return 1
        exec @retcode = sys.sp_MS_marksystemobject @quoted_filters_view_90
        if @@ERROR<>0 or @retcode<>0  return (1)

        if @compatlevel < 90
        begin
            select @filters_view_80='sysmergesubsetfilters_' + @unique_art_name + '_80'
            select @quoted_filters_view_80=QUOTENAME(@filters_view_80)

            if object_id(@filters_view_80,'V') is not NULL
            begin
                exec ('drop view ' + @quoted_filters_view_80)
            end
            exec('create view [dbo].' + @quoted_filters_view_80 +
                ' as select filtername, join_filterid, pubid, artid, art_nickname, join_articlename,
                    join_nickname, join_unique_key, expand_proc, join_filterclause from dbo.sysmergesubsetfilters
                        where art_nickname = ' + @tablenickstr + ' and pubid = ' + @guidstr)
            if @@error<>0 return 1
            exec @retcode = sys.sp_MS_marksystemobject @quoted_filters_view_80
            if @@ERROR<>0 or @retcode<>0  return (1)
        end
        else
            select @filters_view_80 = NULL

InsertIntoSysTables:
        select @objid = NULL
        select Top 1 @tablenick = nickname, @processing_order = processing_order,
            @sync_viewname=object_name(sync_objid), @objid=objid, @artname=name, @artid=artid,
            @upload_options = upload_options
        from dbo.sysmergearticles
        where pubid = @pubid
        and (
                (processing_order = @processing_order and nickname < @tablenick)
                or
                processing_order < @processing_order
            )
        and (status=@new_inactive or status=@new_active)
        order by processing_order desc, nickname desc
        insert #temp_table_for_systable_view(contentsview, filtersview, contentsview_90_forall, contentsview_90_forglobal, filtersview_90, rowtrackview)
        values(@view_name_80, @filters_view_80, @view_name_90_forall, @view_name_90_forglobal, @filters_view_90, @view_name_rowtrack)
    end

 
Last revision 2008RTM
See also

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