May 21, 2012

sp_MSmakesystableviews (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSmakesystableviews(nvarchar @publication
, nvarchar @dynamic_snapshot_views_table_name
, bit @create_dynamic_views
, bigint @max_bcp_gen)

MetaData:

 --  Used by snapshot  
create procedure sys.sp_MSmakesystableviews (
@publication sysname,
@dynamic_snapshot_views_table_name sysname = null,
@create_dynamic_views bit = 0,
@max_bcp_gen bigint
)
AS
declare @guidstr nvarchar(40)
declare @pubid uniqueidentifier
declare @contentsview_80 sysname
declare @tombstoneview sysname
declare @genhistoryview_80 sysname
declare @filtersview_80 sysname
declare @filtersview_90 sysname
declare @contentsview_90 sysname
declare @contentsview_90_forall sysname
declare @contentsview_90_forglobal sysname
declare @rowtrackview sysname
declare @genhistoryview_90 sysname
declare @retcode smallint
declare @dbname sysname
declare @command nvarchar(4000)
declare @dynamic_filters bit
declare @view_creation_command nvarchar(4000)
declare @newid uniqueidentifier
declare @dynsnap_views bit
declare @compatlevel int
declare @need_ctsview_rowtrack bit
declare @need_ctsview_forglobal bit

--
-- Check to see if current publication has permission
--
exec @retcode=sys.sp_MSreplcheck_publish
if @retcode<>0 or @@ERROR<>0 return (1)

if (@dynamic_snapshot_views_table_name is not null and @dynamic_snapshot_views_table_name <> N'')
select @dynsnap_views = 1
else
select @dynsnap_views = 0

select @pubid = pubid, @dynamic_filters = dynamic_filters,
@compatlevel = backward_comp_level
from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is null
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

set @need_ctsview_rowtrack= sys.fn_MSmerge_mightneedrowtrackbcp(@pubid)
set @need_ctsview_forglobal= sys.fn_MSmerge_hasdownloadonlyarticles(@pubid)

-- check if there are any generations higher than what 80 can understand
-- if so return and not do any snapshot
if @compatlevel < 90
begin
declare @maxgen_80 int
set @maxgen_80= 2147483647
if exists (select generation from dbo.MSmerge_genhistory where generation > @maxgen_80)
begin
raiserror(21521,16,1,@maxgen_80)
return(1)
end
end

create table #temp_table_for_systable_view
(
id int identity(1,1),
contentsview sysname NULL,
tombstoneview sysname NULL,
genhistoryview sysname NULL,
filtersview sysname NULL,
contentsview_90_forall sysname NULL,
contentsview_90_forglobal sysname NULL,
genhistoryview_90 sysname NULL,
filtersview_90 sysname NULL,
contentsview_90 sysname NULL,
rowtrackview sysname NULL
)

-- use pubid to generate the view names only if we are not generating
-- dynamic snapshot views
if @dynsnap_views = 1 or @create_dynamic_views = 1
set @newid = newid()
else
set @newid = @pubid

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

select @contentsview_80 = 'MSmerge_cont' + @guidstr
select @contentsview_90 = 'MSmerge_cont' + @guidstr + '_90'
select @contentsview_90_forall = 'MSmerge_cont' + @guidstr + '_90_forall'
select @contentsview_90_forglobal = 'MSmerge_cont' + @guidstr + '_90_forglobal'
select @tombstoneview = 'MSmerge_ts' + @guidstr
select @genhistoryview_80 = 'MSmerge_gh' + @guidstr
select @genhistoryview_90 = 'MSmerge_gh' + @guidstr + '_90'
select @filtersview_80 = 'MSmerge_filt' + @guidstr
select @filtersview_90 = 'MSmerge_filt' + @guidstr + '_90'
select @rowtrackview = 'MSmerge_cont' + @guidstr + '_rowtrack'

insert #temp_table_for_systable_view
(
contentsview,
tombstoneview,
genhistoryview,
filtersview,
contentsview_90_forall,
contentsview_90_forglobal,
genhistoryview_90,
filtersview_90,
contentsview_90,
rowtrackview
)
values
(
@contentsview_80,
@tombstoneview,
@genhistoryview_80,
@filtersview_80,
@contentsview_90_forall,
@contentsview_90_forglobal,
@genhistoryview_90,
@filtersview_90,
@contentsview_90,
@rowtrackview
)

CreateViews:
-- if we generating a regular snapshot we need to drop the views if they exist
if @dynsnap_views = 0 and @create_dynamic_views = 0
begin
exec @retcode = sys.sp_MSdropsystableviews @pubid
if @@ERROR<>0 OR @retcode<>0 return (1)
end

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

-- generate view for MSmerge_contents qualified by the pubid --
-- For dynamically filtered publication, security check is performed in
the sync view of the base table --
-- only generate the views if there are rows in the MSmerge_contents table
if exists (select * from MSmerge_contents)
begin
set @command = 'sys.sp_MSmakectsview ' + QUOTENAME(@publication) + ' , ' + quotename(@contentsview_90)
+ ' , ' + COALESCE(quotename(@dynamic_snapshot_views_table_name) collate database_default, N'null' collate database_default)
+ ' , ' + convert(nvarchar, @dynsnap_views) + ' , ' + convert(nvarchar,@max_bcp_gen)
set @dbname = db_name()

exec @retcode = sys.xp_execresultset @command, @dbname
if @@ERROR<>0 OR @retcode <>0 return (1)
end
else
begin
set @command = 'create view dbo.' + quotename(@contentsview_90) + ' as select * from MSmerge_contents where 1 = 2'
exec(@command)
if @@ERROR<>0
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @contentsview_90
if @@ERROR<>0 or @retcode<>0 return (1)


-- Create the view on MSmerge_contents that contains rows which are updateable at every subscriber.
select @view_creation_command = 'create view dbo.' + quotename(@contentsview_90_forall) + ' as
select * from '
+ quotename(@contentsview_90) + '
where tablenick in (select nickname from dbo.sysmergearticles where upload_options <> 1 and upload_options <> 2)
and generation <= '
+ convert(nvarchar, @max_bcp_gen)

exec (@view_creation_command)
if @@ERROR <>0 return (1)
exec @retcode = sys.sp_MS_marksystemobject @contentsview_90_forall
if @@ERROR<>0 or @retcode<>0 return (1)

if 1=@need_ctsview_forglobal
begin
-- Create the view on MSmerge_contents that contains rows which are only updateable at global subscribers.
select @view_creation_command = 'create view dbo.' + quotename(@contentsview_90_forglobal) + ' as
select * from '
+ quotename(@contentsview_90) + '
where tablenick in (select nickname from dbo.sysmergearticles where upload_options = 1 or upload_options = 2)
and generation <= '
+ convert(nvarchar, @max_bcp_gen)

exec (@view_creation_command)
if @@ERROR <>0 return (1)
exec @retcode = sys.sp_MS_marksystemobject @contentsview_90_forglobal
if @@ERROR<>0 or @retcode<>0 return (1)
end

-- Create the view on MSmerge_contents that has the same format as the SQL2000 MSmerge_contents --
-- dont convert. let mdac do all the conversion. we anyway raise an error above incase the generation greater than max_int
select @view_creation_command = 'create view dbo.' + @contentsview_80 + ' as
select tablenick, rowguid,
generation = case when abs(generation) > 2147483647 then 0 else convert(int, generation) 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 = case
when colv1 is null or sys.fn_fIsColTracked(tablenick) = 0 then colv1
else {fn COLV_90_TO_80(colv1, sys.fn_cColvEntries_80('
'' + cast(@pubid as nvarchar(36)) + ''', tablenick))} end
from '
+ @contentsview_90
--

select @view_creation_command = 'create view dbo.' + quotename(@contentsview_80) + ' as
select tablenick, rowguid, generation = isnull(convert(int, generation),0),
partchangegen = case when partchangegen < 0 then NULL else convert(int, partchangegen) end,
joinchangegen = case when partchangegen < 0 then convert(int, (-partchangegen)) else convert(int, partchangegen) end,
lineage = {fn LINEAGE_90_TO_80(lineage)},
colv1 = case
when colv1 is null or sys.fn_fIsColTracked(tablenick) = 0 then colv1
else {fn COLV_90_TO_80(colv1, sys.fn_cColvEntries_80('
'' + cast(@pubid as nvarchar(36)) + ''', tablenick))} end
from '
+ quotename(@contentsview_90)

exec (@view_creation_command)
if @@ERROR <>0 return (1)
exec @retcode = sys.sp_MS_marksystemobject @contentsview_80
if @@ERROR<>0 or @retcode<>0 return (1)

--
-- generate the view for dbo.MSmerge_tombstone. In SP2 and Shiloh, the change was made to make the view
-- return 0 rows since it is unnecessary and expensive to propagate the tombstones.
-- In order to leave all the other moving parts unchanged, we decided to let the view
-- return 0 rows.
--

select @view_creation_command = 'create view dbo.' + quotename(@tombstoneview) + ' as select * from dbo.MSmerge_tombstone where 1= 2'

if @dynamic_filters = 1
begin
select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
end

exec (@view_creation_command)
if @@ERROR <>0
begin
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @tombstoneview
if @@ERROR<>0 or @retcode<>0 return (1)

-- Yukon genhistory view
select @view_creation_command = 'create view dbo.' + quotename(@genhistoryview_90) + '(guidsrc, pubid, generation,
art_nick, nicknames, coldate, genstatus, changecount, subscriber_number) as select DISTINCT guidsrc, CONVERT(uniqueidentifier, '

+ @guidstr + '), generation, art_nick, nicknames, coldate, genstatus, changecount, subscriber_number from dbo.MSmerge_genhistory gh
where genstatus in (1,2) and
(art_nick = 0 or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = '
+ @guidstr + ')) and
(pubid is null or pubid=(select top 1 pubid from dbo.MSmerge_genhistory where guidsrc=gh.guidsrc))
and generation <= '
+ convert(nvarchar, @max_bcp_gen)

if @dynamic_filters = 1
begin
select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
end

exec (@view_creation_command)
if @@ERROR <>0
begin
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @genhistoryview_90
if @@ERROR<>0 or @retcode<>0 return (1)

-- Create the view on MSmerge_genhistory that has the same format as the SQL2000 MSmerge_genhistory --
select @view_creation_command = 'create view dbo.' + quotename(@genhistoryview_80) + ' as
select guidsrc, guidlocal = newid(), pubid, generation = isnull(convert(int, generation),0), art_nick, nicknames= {fn REPLNICKARRAY_90_TO_80(nicknames)}, coldate from '
+ quotename(@genhistoryview_90)

exec (@view_creation_command)
if @@ERROR <>0
begin
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @genhistoryview_80
if @@ERROR<>0 or @retcode<>0 return (1)

select @view_creation_command = 'create view dbo.' + quotename(@filtersview_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 pubid = '
+ @guidstr + '
and artid in (select artid from dbo.sysmergearticles where pubid = '
+ @guidstr + ' and status <> 5 and status <> 6)'

if @dynamic_filters = 1
begin
select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
end

exec (@view_creation_command)
if @@ERROR <>0
begin
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @filtersview_80
if @@ERROR<>0 or @retcode<>0 return (1)

select @view_creation_command = 'create view dbo.' + quotename(@filtersview_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 pubid = '
+ @guidstr + '
and artid in (select artid from dbo.sysmergearticles where pubid = '
+ @guidstr + ' and status <> 5 and status <> 6)'

if @dynamic_filters = 1
begin
select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
end

exec (@view_creation_command)
if @@ERROR <>0
begin
return (1)
end
exec @retcode = sys.sp_MS_marksystemobject @filtersview_90
if @@ERROR<>0 or @retcode<>0 return (1)

if 1=@need_ctsview_rowtrack
begin
-- Create the view on MSmerge_contents for lightweight subscribers.
select @view_creation_command = 'create view dbo.' + quotename(@rowtrackview) + ' 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=cast((case sys.fn_fIsColTracked(tablenick)
when 1 then 0 -- @COLUMNS_ENUMERATED_ChangedOnly
else 2 -- @COLUMNS_ENUMERATED_AllOnOtherReason
end) as tinyint),
sync_cookie=null
from '
+ quotename(@contentsview_90) + '
where tablenick in (select nickname from dbo.sysmergearticles where upload_options<>1 and upload_options<>2)'


if @dynamic_filters = 1
begin
select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
end

exec (@view_creation_command)
if @@ERROR <>0 return (1)
exec @retcode = sys.sp_MS_marksystemobject @rowtrackview
if @@ERROR<>0 or @retcode<>0 return (1)
end

-- set security for dynamic filters
if @dynamic_filters = 1
begin

set @command = 'grant select on ' + quotename(@contentsview_80) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@contentsview_90) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@contentsview_90_forall) + ' to public'
exec(@command)
if @@error<>0 return(1)
if 1=@need_ctsview_forglobal
begin
set @command = 'grant select on ' + quotename(@contentsview_90_forglobal) + ' to public'
exec(@command)
if @@error<>0 return(1)
end
set @command = 'grant select on ' + quotename(@tombstoneview) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@genhistoryview_80) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@genhistoryview_90) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@filtersview_80) + ' to public'
exec(@command)
if @@error<>0 return(1)
set @command = 'grant select on ' + quotename(@filtersview_90) + ' to public'
exec(@command)
if @@error<>0 return(1)
if 1=@need_ctsview_rowtrack
begin
set @command = 'grant select on ' + quotename(@rowtrackview) + ' to public'
exec(@command)
if @@error<>0 return(1)
end
end

Finish:
set nocount on
-- we only generate per-article contents view for static publications --
exec @retcode = sys.sp_MSgettablecontents @pubid, @dynamic_snapshot_views_table_name, @need_ctsview_rowtrack, @create_dynamic_views, @max_bcp_gen
if @@ERROR<>0 OR @retcode <>0 return (1)

exec('select
contentsview,
tombstoneview,
genhistoryview,
filtersview,
contentsview_90_forall,
contentsview_90_forglobal,
genhistoryview_90,
filtersview_90,
contentsview_90,
rowtrackview
from #temp_table_for_systable_view
order by id'
)

drop table #temp_table_for_systable_view

return (0)

No comments:

Post a Comment

Total Pageviews