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
