May 8, 2012

sp_MScreate_logical_record_views (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_MScreate_logical_record_views(uniqueidentifier @pubid)

MetaData:

   
create procedure sys.sp_MScreate_logical_record_views(@pubid uniqueidentifier)
AS
declare @publication sysname,
@artid uniqueidentifier,
@art_nick int,
@art_index int,
@article_level int,
@progress int,
@retcode int
declare @articles table (indexcol int identity NOT NULL, art_nick int NOT NULL, article_level int NOT NULL)

-- Security check
-- This proc can be called by both publisher and subscriber.
-- On publisher side, it is called by sp_MSpublicationview.
-- On subscriber side, it is called by the agent and by DDL trigger (sp_MSResetTriggerProcs)
-- Necessary rights for sp_MSpublicationview is db_owner; necessary rights on subscriber
-- is db_owner as well. Thus, we now also test for db_owner.
if 1 <> is_member ('db_owner')
begin
raiserror(15247,-1,-1)
return 1
end

select @publication = name from dbo.sysmergepublications where pubid = @pubid
if @publication is null
begin
return (1)
end

if not exists (select * from dbo.sysmergesubsetfilters
where (filter_type & 2) = 2
and pubid = @pubid)
begin
if not exists (select * from dbo.sysmergesubsetfilters
where (filter_type & 2) = 2)
begin
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc6MSmerge_contents'
AND object_id = OBJECT_ID('MSmerge_contents'))
drop index dbo.MSmerge_contents.nc6MSmerge_contents

IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc3MSmerge_tombstone'
AND object_id = OBJECT_ID('MSmerge_tombstone'))
drop index dbo.MSmerge_tombstone.nc3MSmerge_tombstone
end
return 0
end

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc6MSmerge_contents'
AND object_id = OBJECT_ID('MSmerge_contents'))
create index nc6MSmerge_contents on dbo.MSmerge_contents(logical_record_parent_rowguid)

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc3MSmerge_tombstone'
AND object_id = OBJECT_ID('MSmerge_tombstone'))
create index nc3MSmerge_tombstone on dbo.MSmerge_tombstone(logical_record_parent_rowguid)

exec @retcode = sys.sp_MSdetermine_logical_record_parents @pubid
if @@ERROR <>0 OR @retcode <>0 return (1)

set @progress = 1
set @article_level = 0

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 @articles the article levels of all articles.
insert into @articles(art_nick, article_level) select nickname, @article_level from dbo.sysmergearticles
where pubid=@pubid and nickname not in (select art_nick from @articles)
and nickname not in
(select art_nickname from dbo.sysmergesubsetfilters
where pubid=@pubid and join_nickname not in
(select art_nick from @articles))

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

set @art_index = 0
select @art_index = min(indexcol) from @articles where indexcol > @art_index

while (@art_index is not null)
begin
select @art_nick=art_nick, @article_level = article_level from @articles
where indexcol = @art_index

if exists (select * from dbo.sysmergesubsetfilters
where (filter_type & 2) = 2
and (join_nickname = @art_nick or art_nickname = @art_nick))
begin
exec @retcode = sys.sp_MScreate_article_logical_record_views @art_nick
if @retcode <> 0 or @@error <> 0 return 1
end

select @art_index = min(indexcol) from @articles where indexcol > @art_index
end

return (0)

No comments:

Post a Comment

Total Pageviews