May 24, 2012

sp_MSpublicationview (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_MSpublicationview(nvarchar @publication
, int @force_flag
, bit @max_network_optimization
, nvarchar @articlename)

MetaData:

 create procedure sys.sp_MSpublicationview(  
@publication sysname,
@force_flag int = 0, -- force_flag = 1 : pub-wide mode, force_flag = 2 : article mode
@max_network_optimization bit = 0,
@articlename sysname = NULL
) AS
declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @join_articlename nvarchar(270)
declare @join_viewname nvarchar(270)
declare @join_before_view nvarchar(270)
declare @before_name nvarchar(270)
declare @before_viewname nvarchar(270)
declare @unqual_sourcename nvarchar(270)
declare @unqual_sourcename2 nvarchar(540)
declare @article nvarchar(270)
declare @art_nick int
declare @join_nick int
declare @join_filterclause nvarchar(1000)
declare @bool_filterclause nvarchar(4000)
declare @bool_filterclause_modified nvarchar(4000)
declare @view_rule nvarchar(max)
declare @before_view_rule nvarchar(max)
declare @partition_view_rule nvarchar(max)
declare @partition_deleted_view_rule nvarchar(max)
declare @partition_inserted_view_rule nvarchar(max)
declare @before_objid int
declare @article_level int
declare @progress int
declare @art int
declare @viewname nvarchar(270)
declare @procname nvarchar(300)
declare @source_objid int
declare @source_object nvarchar(270)
declare @sync_objid int
declare @bitset int
declare @permanent int
declare @temporary int
declare @filter_id int
declare @filter_id_str nvarchar(10)
declare @guidstr nvarchar(40)
declare @pubidstr nvarchar(40)
declare @rgcol nvarchar(270)
declare @view_type int
declare @belongsname nvarchar(270)
declare @join_nickstr nvarchar(10)
declare @unqual_jointable nvarchar(270)
declare @retcode smallint
declare @hasguid int
declare @vertical_partition int
declare @join_unique_key int
declare @simple_join_view int
declare @join_filterid int
declare @allhaveguids int
declare @command nvarchar(max)
declare @objid int
declare @owner nvarchar(270)
declare @table nvarchar(270)
declare @quoted_obj nvarchar(290)
declare @quoted_rowguid nvarchar(290)
declare @quoted_partition_view nvarchar(290)
declare @before_rowguidname sysname
declare @snapshot_ready int
declare @columns varbinary(128)
declare @column_list nvarchar(max)
declare @colname nvarchar(270)
declare @colid int
declare @dynamic_filters bit
declare @alias_for_sourceobject sysname
declare @prefixed_column_list nvarchar(max)
declare @partition_view_id int
declare @partition_view_name nvarchar(270), @logical_record_view nvarchar(270)
declare @joinart_partition_view_name nvarchar(270)
declare @use_partition_groups smallint, @dynamic_filters_function_list nvarchar(500)
declare @partition_column_list nvarchar(max)
declare @join_view_id int, @joinart_partition_view_id int, @join_before_view_id int
declare @publication_number smallint

-- Security check
exec @retcode= sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)

set @progress = 1
set @article_level = 0
set @permanent = 1
set @temporary = 2
set @allhaveguids = 1
set @before_rowguidname = NULL

--
-- Only legal publisher can run this stored procedure
--
set nocount on
-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid, @snapshot_ready = snapshot_ready, @dynamic_filters = dynamic_filters,
@use_partition_groups = use_partition_groups, @dynamic_filters_function_list = dynamic_filters_function_list,
@publication_number = publication_number
FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()

IF @pubid IS NULL
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END

select @table=object_name(objid) from dbo.sysmergearticles where pubid=@pubid and (columns is NULL or columns = 0x00)
if @table is not NULL
begin
raiserror(21318, 16, -1, @table)
return (1)
end

-- If snapshot is already ready, views are good. Don't drop and recreate as someone
-- might be using them.

if @snapshot_ready = 1 and @force_flag = 0
return (0)

-- If the snapshot is not ready and calling code is running sp_MSpublication_view in article mode and not pub-wide mode
-- then force the behaviour to the pub-wide mode
if @snapshot_ready <> 1 and @force_flag = 2
set @force_flag = 1

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

create table #art(indexcol int identity NOT NULL, art_nick int NOT NULL, article_level int NOT NULL)
if @@ERROR <> 0
begin
goto FAILURE
end


begin tran
save tran sp_MSpublicationview

while @progress > 0
BEGIN
--
-- Select articles that have either a boolean_filter or at least one join filter
-- into a temp table in an optimized order.
--

-- This inserts into #art the article levels of all articles.
insert into #art(art_nick, article_level) select nickname, @article_level from dbo.sysmergearticles
where pubid=@pubid and nickname not in (select art_nick from #art)
and nickname not in
(select art_nickname from dbo.sysmergesubsetfilters
where pubid=@pubid and (filter_type & 1) = 1 and join_nickname not in
(select art_nick from #art))
--
-- NOTENOTE: add error checking here.
--

set @progress = @@rowcount
select @article_level = @article_level + 1
END

--
-- When the force_flag is 2, sp_MSpublicationview is being invoked from code paths such as DDL - ADD/DROP/ALTER COLUMN
-- and sp_repladdcolumn/sp_repldropcolumn - In that case - prune the #art table by removing nicknames that are not related
-- to the article name that is passed int
--
if (@force_flag = 2)
begin
select @art_nick = nickname from dbo.sysmergearticles
where pubid=@pubid and name = @articlename
delete from #art where art_nick not in (select distinct nickname from sys.fn_MSdetermine_related_articles(@art_nick))
end
select @art_nick = NULL

-- Views for a particular article are being recreated only.
if (@force_flag = 2)
begin
select @art_nick = nickname, @artid = artid from dbo.sysmergearticles where pubid=@pubid and name = @articlename
if (@art_nick is NOT NULL)
begin
-- Drop the old view --
select @viewname = OBJECT_NAME (sync_objid),
@before_viewname = OBJECT_NAME(before_view_objid),
@partition_view_name = OBJECT_NAME(partition_view_id),
@logical_record_view = object_name(logical_record_view)
from dbo.sysmergepartitioninfoview where
pubid = @pubid and nickname = @art_nick

if @viewname IS NOT NULL
begin
select @quoted_obj = QUOTENAME(@viewname)
exec ('drop view ' + @quoted_obj)
end
if @before_viewname IS NOT NULL
begin
select @quoted_obj = QUOTENAME(@before_viewname)
exec ('drop view ' + @quoted_obj)
end

if @partition_view_name is not null
begin
select @quoted_partition_view = QUOTENAME(@partition_view_name)
exec ('drop view ' + @quoted_partition_view)
end

if object_id(@logical_record_view) is not NULL
and not exists (select * from sysmergepartitioninfo where logical_record_view = object_id(@logical_record_view)
and pubid <> @pubid)
begin
select @quoted_obj = QUOTENAME(@logical_record_view)
exec ('drop view ' + @quoted_obj)
end

-- Update the row in dbo.sysmergearticles --
update dbo.sysmergearticles set view_type = 0,
sync_objid = objid,
before_view_objid = NULL
where pubid = @pubid and nickname = @art_nick
if @@ERROR <> 0 goto FAILURE

update dbo.sysmergepartitioninfo
set partition_view_id = NULL,
partition_deleted_view_rule = NULL,
partition_inserted_view_rule = NULL,
logical_record_view = NULL,
logical_record_parent_nickname = NULL,
logical_record_deleted_view_rule = NULL
where pubid = @pubid and artid = @artid
if @@ERROR <> 0 goto FAILURE
end
end
else
begin
-- Drop the old views and reset sync_objid --
select top 1 @art_nick = nickname, @artid = artid from dbo.sysmergearticles
where pubid = @pubid and objid<>sync_objid
order by nickname
while @art_nick is not null
begin
-- Drop the old view --
select @viewname = OBJECT_NAME (sync_objid),
@before_viewname = OBJECT_NAME(before_view_objid),
@partition_view_name = OBJECT_NAME(partition_view_id),
@logical_record_view = object_name(logical_record_view)
from dbo.sysmergepartitioninfoview where
pubid = @pubid and nickname = @art_nick

if @viewname IS NOT NULL
begin
select @quoted_obj = QUOTENAME(@viewname)
exec ('drop view ' + @quoted_obj)
end
if @before_viewname IS NOT NULL
begin
select @quoted_obj = QUOTENAME(@before_viewname)
exec ('drop view ' + @quoted_obj)
end

if @partition_view_name is not null
begin
select @quoted_partition_view = QUOTENAME(@partition_view_name)
exec ('drop view ' + @quoted_partition_view)
end

if object_id(@logical_record_view) is not NULL
and not exists (select * from sysmergepartitioninfo where logical_record_view = object_id(@logical_record_view)
and pubid <> @pubid)
begin
select @quoted_obj = QUOTENAME(@logical_record_view)
exec ('drop view ' + @quoted_obj)
end

-- Update the row in dbo.sysmergearticles --
update dbo.sysmergearticles set view_type = 0,
sync_objid = objid,
before_view_objid = NULL
where pubid = @pubid and nickname = @art_nick
if @@ERROR <> 0 goto FAILURE

update dbo.sysmergepartitioninfo
set partition_view_id = NULL,
partition_deleted_view_rule = NULL,
partition_inserted_view_rule = NULL,
logical_record_view = NULL,
logical_record_parent_nickname = NULL,
logical_record_deleted_view_rule = NULL
where pubid = @pubid and artid = @artid
if @@ERROR <> 0 goto FAILURE

select @art_nick = NULL, @artid = NULL
-- Find the next one --
select top 1 @art_nick = nickname, @artid = artid from dbo.sysmergearticles
where pubid = @pubid and objid<>sync_objid
order by nickname
end
end

-- do the following only after all tables have been "activated" i.e. rowguid column has been added
-- basically this proc gets calls twice from the snapshot agent. Once before rowguidcols are present
-- and once after the rowguid columns have been created. We want the partition groups related setup
-- to happen only afte rowguidcols have been established.
select @art_nick = min(nickname) from dbo.sysmergearticles where pubid = @pubid
while @art_nick is not null
begin
select @objid = objid from dbo.sysmergearticles where pubid = @pubid and nickname = @art_nick
if not exists (select name from sys.columns where object_id=@objid and is_rowguidcol=1)
set @allhaveguids = 0
select @art_nick = min(nickname) from dbo.sysmergearticles where pubid = @pubid and nickname > @art_nick
end
if @allhaveguids = 1
begin
exec @retcode = sys.sp_MSsetup_use_partition_groups @publication
if @retcode <> 0 or @@error <> 0
goto FAILURE
select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid
end

if (@use_partition_groups = 1)
begin
create table #list_of_functions (function_name_with_parens nvarchar(500), function_name_without_parens sysname)
exec @retcode = sp_MSsetup_function_list_table @pubid
if @retcode <> 0 or @@error <> 0 goto FAILURE
end

set @art = 0
select @art=min(indexcol) from #art where indexcol>@art

while (@art is not null)
begin
select @art_nick=art_nick, @article_level = article_level from #art
where indexcol = @art
select @article = name, @artid = artid, @columns = columns, @source_objid = objid,
@sync_objid = sync_objid, @procname = view_sel_proc,
@partition_view_id = partition_view_id
from dbo.sysmergepartitioninfoview
where nickname=@art_nick and pubid = @pubid

-- first create the before image table if one is needed
exec @retcode = sys.sp_MScreatebeforetable @source_objid
if @@error <> 0 or @retcode <> 0
goto FAILURE
select @before_objid = before_image_objid
from dbo.sysmergepartitioninfoview
where nickname=@art_nick and pubid = @pubid

exec @retcode = sys.sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @source_objid

exec @retcode = sys.sp_MSgetfiltercolumnlist @pubid, @partition_column_list OUTPUT, @source_objid
if @@error <> 0 or @retcode <> 0
goto FAILURE

set @before_name = OBJECT_NAME(@before_objid)
if @before_name is not null
begin
select @before_rowguidname=name from sys.columns where object_id=@source_objid and is_rowguidcol=1
exec @retcode = sys.sp_MSguidtostr @pubid, @guidstr out
set @before_viewname = @before_name + '_v_' + @guidstr
end
else
set @before_viewname = NULL

-- Owner-qualify the name for perf (less recompiles).
select @before_name = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(@before_objid))
from sys.objects where object_id = @before_objid

exec @retcode = sys.sp_MSguidtostr @artid, @guidstr out
if @@ERROR <>0 OR @retcode <>0 goto FAILURE

select @source_object = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) from sys.objects
where object_id = @source_objid
select @unqual_sourcename = QUOTENAME(OBJECT_NAME(@source_objid))
select @unqual_sourcename2 = sys.fn_replreplacesinglequote(@source_object)

select @bool_filterclause=subset_filterclause, @vertical_partition=vertical_partition
from dbo.sysmergearticles where name = @article and pubid = @pubid

-- verify the syntax of boolean filter, if added with vertical-partition to true
-- in this case, the filter clause can contain columns that do not exist in the partition.
if len(@bool_filterclause) > 0
begin
select @bool_filterclause = ' (' + @bool_filterclause + ') '

end

set @rgcol = NULL
select @rgcol = QUOTENAME(name) from sys.columns where object_id = @source_objid and
is_rowguidcol = 1
if @rgcol is not NULL
set @hasguid = 1
else
begin
set @hasguid = 0
set @allhaveguids = 0
end

--
-- Process non looping articles that have either a boolean or a join_filter.
--
if ( @article_level > 0 OR (len(@bool_filterclause) > 0) )
begin
--
-- If the article has a previously generated view, then drop the view before
-- creating the new one.
--
set @partition_view_name = NULL
select @partition_view_name = name from sys.objects where object_id = @partition_view_id and type = 'V' and is_ms_shipped = 1
if @partition_view_name IS NOT NULL
begin
select @quoted_partition_view = QUOTENAME(@partition_view_name)
exec ('drop view ' + @quoted_partition_view)
if @@ERROR<>0 goto FAILURE
end

set @viewname = NULL
select @viewname = name from sys.objects where object_id = @sync_objid and type='V' and is_ms_shipped = 1
if @viewname IS NOT NULL
begin
select @quoted_obj = QUOTENAME(@viewname)
exec ('drop view ' + @quoted_obj)
if @@ERROR<>0 goto FAILURE
end

--
-- Any join filter(s)? If any, process join filter(s)
--
if (@article_level > 0)
begin
declare pub1 CURSOR LOCAL FAST_FORWARD FOR select join_filterclause, join_nickname, join_articlename,
join_unique_key, join_filterid from dbo.sysmergesubsetfilters where pubid=@pubid and artid=@artid and (filter_type & 1)=1
FOR READ ONLY
open pub1
fetch pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
select @join_filterclause=' ( ' + @join_filterclause + ') '
select @unqual_jointable = QUOTENAME(name) from sys.objects
where object_id = (select objid from dbo.sysmergearticles where name=@join_articlename and pubid=@pubid)

select @join_viewname = NULL, @joinart_partition_view_name = NULL, @join_before_view = NULL

if @max_network_optimization = 0
select @join_view_id = sync_objid,
@join_before_view_id = before_image_objid,
@joinart_partition_view_id = partition_view_id
from dbo.sysmergepartitioninfoview where nickname = @join_nick and pubid = @pubid
else
select @join_view_id = sync_objid,
@join_before_view_id = case when before_view_objid is null then before_image_objid else before_view_objid end,
@joinart_partition_view_id = partition_view_id
from dbo.sysmergepartitioninfoview where nickname = @join_nick and pubid = @pubid

-- Owner qualify the names for perf (less recompilations).
select @join_viewname = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@join_view_id))
from sys.objects where object_id = @join_view_id

select @joinart_partition_view_name = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@joinart_partition_view_id))
from sys.objects where object_id = @joinart_partition_view_id

select @join_before_view = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@join_before_view_id))
from sys.objects where object_id = @join_before_view_id

-- if the parent-child relationship is one-many, AND there is only one parent, and if the child
-- itself doesn't have any boolean filters, then use the simple view.
if (@join_unique_key = 1 and (@bool_filterclause is null or len(@bool_filterclause) = 0) and
not exists (select * from dbo.sysmergesubsetfilters where pubid=@pubid and artid=@artid and join_filterid <> @join_filterid and (filter_type & 1) = 1))
begin
set @simple_join_view = 1

if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

set @view_rule = 'select ' + @column_list + ' from ' + @source_object + ' ' + @unqual_sourcename + ' , ' + @join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

if @joinart_partition_view_name is not null and @use_partition_groups = 1
begin
set @partition_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from ' + @source_object + ' ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'
set @partition_deleted_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from deleted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
set @partition_inserted_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from inserted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
end
end
else
begin
set @simple_join_view = 0

-- Alias the source object with the unqualified name and use that to select the rowguidcol --
set @view_rule = 'select ' + @unqual_sourcename + '.rowguidcol from ' + @source_object + ' ' + @unqual_sourcename + ' , ' + @join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

if @joinart_partition_view_name is not null and @use_partition_groups = 1
begin
set @partition_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from ' + @source_object + ' ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'
set @partition_deleted_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from deleted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
set @partition_inserted_view_rule = 'select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from inserted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
end
end

if @before_name is not null
begin
set @before_view_rule = 'select * from ' + @before_name + ' ' + @unqual_sourcename + ' where (exists (select * from ' +
@join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') '
if @join_before_view is not NULL
set @before_view_rule = @before_view_rule + ' or exists (select * from ' +
@join_before_view + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ')'
end

fetch next from pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
WHILE (@@fetch_status <> -1)
begin
select @join_filterclause=' ( ' + @join_filterclause + ') '

select @unqual_jointable= quotename(name)
from sys.objects
where object_id = (select objid from dbo.sysmergearticles where name=@join_articlename and pubid=@pubid)

select @join_viewname = NULL, @joinart_partition_view_name = NULL, @join_before_view = NULL

if @max_network_optimization = 0
select @join_view_id = sync_objid,
@join_before_view_id = before_image_objid,
@joinart_partition_view_id = partition_view_id
from dbo.sysmergepartitioninfoview
where nickname = @join_nick and pubid = @pubid
else
select @join_view_id = sync_objid,
@join_before_view_id = case when before_view_objid is null then before_image_objid else before_view_objid end,
@joinart_partition_view_id = partition_view_id
from dbo.sysmergepartitioninfoview
where nickname = @join_nick and pubid = @pubid

-- Owner qualify the names for perf (less recompilations).
select @join_viewname = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@join_view_id))
from sys.objects where object_id = @join_view_id

select @joinart_partition_view_name = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@joinart_partition_view_id))
from sys.objects where object_id = @joinart_partition_view_id

select @join_before_view = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(@join_before_view_id))
from sys.objects where object_id = @join_before_view_id

set @view_rule = @view_rule + ' union select ' + @unqual_sourcename + '.rowguidcol from ' + @source_object + ' ' + @unqual_sourcename + ', ' + @join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

if @before_name is not null
begin
set @before_view_rule = @before_view_rule + ' or exists (select * from ' +
@join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') '
if @join_before_view is not NULL
select @before_view_rule = @before_view_rule + ' or exists (select * from ' +
@join_before_view + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') '
end

if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

if @joinart_partition_view_name is not null and @use_partition_groups = 1
begin
set @partition_view_rule = @partition_view_rule + ' union select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from ' + @source_object + ' ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1 or
(HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'

set @partition_deleted_view_rule = @partition_deleted_view_rule + ' union select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from deleted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
set @partition_inserted_view_rule = @partition_inserted_view_rule + ' union select ' + @partition_column_list + ', ' + @unqual_jointable + '.' + 'partition_id from inserted ' + @unqual_sourcename + ' , ' + @joinart_partition_view_name + ' ' + @unqual_jointable + ' where (' + @join_filterclause + ')'
end

fetch next from pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
end

close pub1
deallocate pub1

if len(@bool_filterclause) > 0
begin
set @view_rule = @view_rule + ' union select ' + @unqual_sourcename + '.rowguidcol from ' + @source_object + ' ' + @unqual_sourcename + ' where '+ @bool_filterclause +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

if @before_name is not null
set @before_view_rule = @before_view_rule + ' or ' + @bool_filterclause

-- Determine if this boolean filter on this article in this publication has a dynamic filter in it.
-- If it does, then join with dbo.dbo.MSmerge_partition_groups with the WHERE boolean filter clause replacing
-- all instances of function calls with msp.<column name>. If it doesn't, use a hard-coded -1 for the partition id,
-- e.g. select <column list>, -1 from table where bool filter clause.
if @use_partition_groups = 1
begin
if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

if exists (select * from #list_of_functions
where REPLACE(REPLACE(REPLACE(REPLACE(UPPER(@bool_filterclause collate SQL_Latin1_General_CP1_CS_AS), char(0x20),''), char(0x09),''), char(0x0D),''), char(0x0A),'')
like '%' + UPPER(function_name_with_parens) + '%'
and sys.fn_MSisfilteredcolumn(UPPER(@bool_filterclause), UPPER(function_name_without_parens), NULL) = 1)
begin
set @partition_view_rule = @partition_view_rule + ' union select ' + @partition_column_list + ', msp.partition_id from ' + @source_object + ' ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
set @partition_deleted_view_rule = @partition_deleted_view_rule + ' union select ' + @partition_column_list + ', msp.partition_id from deleted ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
set @partition_inserted_view_rule = @partition_inserted_view_rule + ' union select ' + @partition_column_list + ', msp.partition_id from inserted ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
exec @retcode = sys.sp_MSmodify_boolfilterclause @bool_filterclause, @bool_filterclause_modified OUTPUT
set @bool_filterclause_modified= '(' + @bool_filterclause_modified + ')'
if @retcode <> 0 or @@error <> 0
goto FAILURE
set @partition_view_rule = @partition_view_rule + @bool_filterclause_modified +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'
set @partition_deleted_view_rule = @partition_deleted_view_rule + @bool_filterclause_modified + ')'
set @partition_inserted_view_rule = @partition_inserted_view_rule + @bool_filterclause_modified + ')'
end
else
begin
set @partition_view_rule = @partition_view_rule + ' union select ' + @partition_column_list + ', partition_id = -1 from ' + @source_object + ' ' + @unqual_sourcename + ' where ('+ @bool_filterclause +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'

set @partition_deleted_view_rule = @partition_deleted_view_rule + ' union select ' + @partition_column_list + ', partition_id = -1 from deleted ' + @unqual_sourcename + ' where ('+ @bool_filterclause + ')'
set @partition_inserted_view_rule = @partition_inserted_view_rule + ' union select ' + @partition_column_list + ', partition_id = -1 from inserted ' + @unqual_sourcename + ' where ('+ @bool_filterclause + ')'
end
end
end
-- Now do the actual view rule as a semi-join, if not a simple join on unique key
if (@simple_join_view = 0)
begin
--
-- Generate a unique alias for the outer select to make sure that it does not generate an
-- ambiguous reference with table names used in the join_filter clause
--
set @alias_for_sourceobject = 'alias_' + @guidstr
exec @retcode = sys.sp_MSgetcolumnlist @pubid, @prefixed_column_list OUTPUT, @source_objid, @alias_for_sourceobject
set @view_rule = 'select ' + @prefixed_column_list + ' from ' + @source_object + ' ' + @alias_for_sourceobject + ' where rowguidcol in (' + @view_rule + ')' +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

end
end
else -- boolean filter only --
begin
select @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename + ' where '+ @bool_filterclause +
' and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

if @before_name is not null
set @before_view_rule = ' select * from ' + @before_name + ' ' + @unqual_sourcename + ' where (' + @bool_filterclause

-- Determine if this boolean filter on this article in this publication has a dynamic filter in it.
-- If it does, then join with dbo.MSmerge_partition_groups with the WHERE boolean filter clause replacing
-- all instances of function calls with msp.<column name>. If it doesn't, use a hard-coded -1 for the partition id,
-- e.g. select <column list>, -1 from table where bool filter clause.
if @use_partition_groups = 1
begin
if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

if exists (select * from #list_of_functions
where REPLACE(REPLACE(REPLACE(REPLACE(UPPER(@bool_filterclause collate SQL_Latin1_General_CP1_CS_AS), char(0x20),''), char(0x09),''), char(0x0D),''), char(0x0A),'')
like '%' + UPPER(function_name_with_parens) + '%'
and sys.fn_MSisfilteredcolumn(UPPER(@bool_filterclause), UPPER(function_name_without_parens), NULL) = 1 )

begin
set @partition_view_rule = ' select ' + @partition_column_list + ', msp.partition_id from ' + @source_object + ' ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
set @partition_deleted_view_rule = ' select ' + @partition_column_list + ', msp.partition_id from deleted ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
set @partition_inserted_view_rule = ' select ' + @partition_column_list + ', msp.partition_id from inserted ' + @unqual_sourcename + ' , dbo.MSmerge_partition_groups msp where (msp.publication_number = ' + convert(nvarchar, @publication_number) + ' and '
exec @retcode = sys.sp_MSmodify_boolfilterclause @bool_filterclause, @bool_filterclause_modified OUTPUT
set @bool_filterclause_modified= '(' + @bool_filterclause_modified + ')'
if @retcode <> 0 or @@error <> 0
goto FAILURE
set @partition_view_rule = @partition_view_rule + @bool_filterclause_modified +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'
set @partition_deleted_view_rule = @partition_deleted_view_rule + @bool_filterclause_modified + ')'
set @partition_inserted_view_rule = @partition_inserted_view_rule + @bool_filterclause_modified + ')'
end
else
begin
set @partition_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from ' + @source_object + ' ' + @unqual_sourcename + ' where ('+ @bool_filterclause +
') and ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'
set @partition_inserted_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from inserted ' + @unqual_sourcename + ' where ('+ @bool_filterclause + ')'
set @partition_deleted_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from deleted ' + @unqual_sourcename + ' where ('+ @bool_filterclause + ')'
end
end
end

select @viewname = 'MSmerge_' + @publication + '_' + @article + '_VIEW'
if (len(@viewname) > 128)
begin
select @viewname = sys.fn_MSmerge_get_syncview_name (@publication, @article, @pubid, @artid)
end
select @quoted_obj = QUOTENAME(@viewname)

-- drop the view if it already exists
if object_id(@quoted_obj) is not NULL
begin
exec('drop view ' + @quoted_obj)
if @@ERROR <> 0
goto FAILURE
end

-- If we havent generated rowguidcol yet, use dummy rule that doesnt refer to it --
if @hasguid = 0
set @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename +
' where ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'
exec ('create view dbo.'+ @quoted_obj + ' as '+ @view_rule)
if @@ERROR<>0
goto FAILURE

-- grant select permission on sync view to public - security check is performed inside the view --
exec ('grant select on ' + @quoted_obj + ' to public')
if @@ERROR<>0
goto FAILURE
-- Mark view as system object --
execute dbo.sp_MS_marksystemobject @quoted_obj
if @@ERROR<>0
goto FAILURE

-- done in setartprocs no need to repeat it here
if @hasguid = 1
begin
select @procname=view_sel_proc from dbo.sysmergearticles where pubid=@pubid and artid=@artid
if object_id(@procname) is not NULL
begin
set @quoted_obj= quotename(@procname)
exec ('drop procedure ' + @quoted_obj)
update dbo.sysmergearticles set view_sel_proc = NULL where artid = @artid and pubid = @pubid
end
else
begin
set @procname = 'MSmerge_sel_' + sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)
end

select @owner = SCHEMA_NAME(schema_id) from sys.objects
where name = @viewname
exec sys.sp_MSmakeviewproc @viewname, @owner, @procname, @rgcol, @pubid, @artid
if @retcode<>0 or @@ERROR<>0
goto FAILURE
update dbo.sysmergearticles set view_sel_proc = @procname where pubid=@pubid and artid=@artid
end --

select @quoted_obj = QUOTENAME(@viewname)

update dbo.sysmergearticles set sync_objid = OBJECT_ID (@quoted_obj), view_type = @permanent
where artid = @artid and pubid = @pubid

if @before_name is not null and @before_view_rule is not null
begin
exec @retcode = sys.sp_MScreatebeforetable @source_objid
if @@ERROR <>0 OR @retcode <>0 goto FAILURE

set @quoted_obj= quotename(@before_viewname)

if object_id(@before_viewname) is not NULL
exec ('drop view ' + @quoted_obj)

set @before_view_rule= @before_view_rule + ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'

exec ('create view dbo.' + @quoted_obj + ' as ' + @before_view_rule)
if @@ERROR<>0
goto FAILURE

if @before_rowguidname is not NULL
begin
set @quoted_rowguid= quotename(@before_rowguidname)
exec ('grant select (' + @quoted_rowguid + ') on '+ @quoted_obj + ' to public')
if @@ERROR<>0
goto FAILURE
end

exec ('grant select (generation) on '+ @quoted_obj + ' to public')
if @@ERROR<>0
goto FAILURE

execute dbo.sp_MS_marksystemobject @before_viewname
if @@ERROR<>0
goto FAILURE
update dbo.sysmergearticles set before_view_objid = OBJECT_ID (@before_viewname)
where artid = @artid and pubid = @pubid
end
end
else
begin
-- All rows qualify from this table. We still create a view that shows -1 as the partition id. This helps us conveniently
-- join child table rows if there are join filters, and find the part id's (-1 in this case) for only the qualifying child rows.
if @use_partition_groups = 1
begin
if @column_list = ' * '
select @column_list = ' ' + @unqual_sourcename + '.* '

set @partition_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from ' + @source_object + ' ' + @unqual_sourcename +
' where ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1
or (HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''SELECT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''UPDATE'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''INSERT'')
| HAS_PERMS_BY_NAME('
'' + @unqual_sourcename2+''', ''OBJECT'', ''DELETE''))=1)'

set @partition_inserted_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from inserted ' + @unqual_sourcename + ' '
set @partition_deleted_view_rule = ' select ' + @partition_column_list + ', partition_id = -1 from deleted ' + @unqual_sourcename
end

select @sync_objid = @source_objid
if @vertical_partition=1 and @column_list<> ' * '
begin
select @viewname = 'MSmerge_' + @publication + '_' + @article + '_VIEW'
if (len(@viewname) > 128)
begin
select @viewname = sys.fn_MSmerge_get_syncview_name (@publication, @article, @pubid, @artid)
end
select @quoted_obj = QUOTENAME(@viewname)

-- drop the view if it already exists
if object_id(@quoted_obj) is not NULL
begin
exec ('drop view ' + @quoted_obj)
if @retcode <> 0 or @@ERROR <> 0
goto FAILURE
end
set @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename +
' where ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'

exec ('create view dbo.'+ @quoted_obj + ' as '+ @view_rule)
if @@ERROR<>0
goto FAILURE
execute dbo.sp_MS_marksystemobject @quoted_obj
if @@ERROR<>0
goto FAILURE

-- grant select permission on sync view to public - security check is performed inside the view --
exec ('grant select on ' + @quoted_obj + ' to public')
if @@ERROR<>0
goto FAILURE
select @sync_objid=object_id(@quoted_obj)
update dbo.sysmergearticles set view_sel_proc = @procname, sync_objid=@sync_objid
where artid = @artid and pubid = @pubid
end
else -- if @dynamic_filters = 1
begin
-- This article doesn't have any vertical or horizontal filters but if the publication is enabled for dynamic filtering,
we still want to generate a dummy view so that logins in the publication access list can generate a dynamic snapshot. --
-- do this even for publications without dynamic filtering so that this view can be used for validation --
select @viewname = 'MSmerge_' + @publication + '_' + @article + '_VIEW'
if (len(@viewname) > 128)
begin
select @viewname = sys.fn_MSmerge_get_syncview_name (@publication, @article, @pubid, @artid)
end
select @quoted_obj = QUOTENAME(@viewname)

-- drop the view if it already exists
if object_id(@quoted_obj) is not NULL
begin
exec ('drop view ' + @quoted_obj)
if @@ERROR <> 0
goto FAILURE
end
set @view_rule = ' select * from ' + @source_object + ' where ({fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1)'
exec ('create view dbo.'+ @quoted_obj + ' as '+ @view_rule)
if @@ERROR<>0
goto FAILURE
execute dbo.sp_MS_marksystemobject @quoted_obj
if @@ERROR<>0
goto FAILURE
exec ('grant select on ' + @quoted_obj + ' to public')
if @@ERROR<>0
goto FAILURE
select @sync_objid=object_id(@quoted_obj)
update dbo.sysmergearticles set view_sel_proc = @procname, sync_objid=@sync_objid, view_type = @permanent
where artid = @artid and pubid = @pubid
end

-- done in sp_MSsetartprocs. No need to repeat it here
if @hasguid = 1
begin
-- still make the select proc, although it selects directly from table
if object_id(@procname) is not NULL
begin
set @quoted_obj= quotename(@procname)
exec ('drop proc ' + @quoted_obj)
update dbo.sysmergearticles set view_sel_proc = NULL where artid = @artid and pubid = @pubid
end

set @procname = 'MSmerge_sel_' + sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)
select @owner = SCHEMA_NAME(schema_id), @viewname = name from sys.objects
where object_id = @sync_objid
exec sys.sp_MSmakeviewproc @viewname, @owner, @procname, @rgcol, @pubid, @artid
update dbo.sysmergearticles set view_sel_proc = @procname where pubid=@pubid and artid=@artid
end --
end

if @use_partition_groups = 1
begin

select @partition_view_name = 'MSmerge_' + @publication + '_' + @article + '_PARTITION_VIEW'
if (len(@partition_view_name) > 128)
begin
select @partition_view_name = 'MSmerge_' + sys.fn_MSguidtostr(@pubid) + '_' + sys.fn_MSguidtostr(@artid) + '_PARTITION_VIEW'
end
select @quoted_partition_view = QUOTENAME(@partition_view_name)

if object_id(@quoted_partition_view) is not NULL
begin
exec ('drop view ' + @quoted_partition_view)
if @@error<>0
goto FAILURE
end

if @allhaveguids = 1
begin
exec ('create view dbo.'+ @quoted_partition_view + ' as '+ @partition_view_rule)
if @@ERROR<>0
goto FAILURE
-- grant select permission on sync view to public - security check is performed inside the view --
exec ('grant select on ' + @quoted_partition_view + ' to public')
if @@ERROR<>0
goto FAILURE
-- Mark view as system object --
execute dbo.sp_MS_marksystemobject @quoted_partition_view
if @@ERROR<>0
goto FAILURE
update dbo.sysmergepartitioninfo
set partition_view_id = OBJECT_ID(@quoted_partition_view),
partition_deleted_view_rule = @partition_deleted_view_rule,
partition_inserted_view_rule = @partition_inserted_view_rule
where artid = @artid and pubid = @pubid
end
else
begin
update dbo.sysmergepartitioninfo
set partition_view_id = NULL,
partition_deleted_view_rule = NULL,
partition_inserted_view_rule = NULL
where artid = @artid and pubid = @pubid
end
end

select @art=min(indexcol) from #art where indexcol>@art
end

drop table #art
if @allhaveguids = 1
begin
declare @dbname sysname
set @dbname = db_name()
-- create the filter expand procs now --
set @filter_id = 0
select @filter_id = min(join_filterid) from dbo.sysmergesubsetfilters where
pubid = @pubid and join_filterid > @filter_id and (filter_type & 1) = 1
while @filter_id is not null
begin
set @filter_id_str = convert(nvarchar(10), @filter_id)
select @procname = expand_proc
from dbo.sysmergesubsetfilters where pubid = @pubid and join_filterid = @filter_id and (filter_type & 1) = 1
-- drop old proc, or generate a new procname --
if object_id(@procname) is not NULL
begin
set @quoted_obj= quotename(@procname)
exec ('drop procedure ' + @quoted_obj)
end
else
begin
set @procname = 'MSmerge_expand_' + @filter_id_str
set @quoted_obj= quotename(@procname)
if object_id(@procname) is not NULL
begin
exec ('drop procedure ' + @quoted_obj)
if @@error<>0
goto FAILURE
end
update dbo.sysmergesubsetfilters set expand_proc = @procname
where pubid = @pubid and join_filterid = @filter_id and (filter_type & 1) = 1
end


set @command = 'exec sys.sp_MSmakeexpandproc ' + quotename(@publication) + ' , ' + @filter_id_str + ', ' + @quoted_obj
exec @retcode = sys.xp_execresultset @command, @dbname
if @retcode <> 0 goto FAILURE

exec dbo.sp_MS_marksystemobject @quoted_obj
if @@ERROR<>0
goto FAILURE

exec ('grant execute on ' + @quoted_obj + ' to public ')
select @filter_id = min(join_filterid) from dbo.sysmergesubsetfilters where
pubid = @pubid and join_filterid > @filter_id and (filter_type & 1) = 1
end
end

exec @retcode = sys.sp_MScreate_logical_record_views @pubid
if @@error <> 0 or @retcode <> 0
goto FAILURE

commit tran
return (0)

FAILURE:
rollback tran sp_MSpublicationview
commit tran
return (1)

No comments:

Post a Comment

Total Pageviews