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_MScomputemergearticlescreationorder(nvarchar @publication)MetaData:
create procedure sys.sp_MScomputemergearticlescreationorder
@publication sysname
AS
SET NOCOUNT ON
DECLARE @pubid uniqueidentifier
DECLARE @max_level int
DECLARE @current_level int
DECLARE @update_level int
DECLARE @limit int
DECLARE @retcode int
SELECT @retcode = 0
--
-- Security Check.
--
-- We perform PAL check instead of db_owner check here so that client-
-- requested dynamic snapshot can go through
SELECT @pubid = NULL
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, NULL, NULL, @pubid output
if @retcode <> 0 or @@error <> 0
return 1
-- Find out the total number of articles in this publication and
-- compute the maximum tree height based on the number of articles in
-- the publication. Here, the tree height is counted from the
-- leaf-nodes towards the root(s) starting from @max_level
SELECT @max_level = COUNT(*) + 10,
@limit = 2 * COUNT(*) + 11
FROM dbo.sysmergeextendedarticlesview
WHERE pubid = @pubid
IF @@ERROR <> 0
BEGIN
RETURN (1)
END
-- The following temp table contains the minimal amount of
-- article information that we want to keep around and the current
-- computed tree level of the article
CREATE TABLE #article_level_info
(
article sysname collate database_default not null,
source_objid INT NOT NULL,
tree_level INT NOT NULL,
nickname INT NOT NULL,
major_type TINYINT NOT NULL, -- 1-view&func, 0-other
processing_order INT NOT NULL
)
CREATE CLUSTERED INDEX ucarticle_level_info
ON #article_level_info(source_objid)
IF @@ERROR <> 0
BEGIN
GOTO Failure
END
-- Populate the article level info table. All articles will be
-- assigned 0 as their initial tree level. Having
-- a tree level of 0 means that the algorithm hasn't discovered
-- any objects that the article depends on within the publication.
INSERT INTO #article_level_info
SELECT name, objid, 0, ISNULL(nickname, 5*@max_level),
CASE type
WHEN 0x40 THEN 1
WHEN 0x80 THEN 1
WHEN 0xA0 THEN 1
ELSE 0
END, processing_order
FROM dbo.sysmergeextendedarticlesview
WHERE pubid = @pubid
-- To jump-start the algorithm, update the tree_level of
-- all articles with no dependency to @max_level.
UPDATE #article_level_info
SET tree_level = @max_level
WHERE NOT EXISTS (SELECT *
FROM sys.sql_dependencies
WHERE source_objid = object_id
AND object_id <> referenced_major_id)
IF @@ERROR <> 0
GOTO Failure
-- For each increasing tree level starting from @max_level, update the
-- the tree_level of articles depending on objects at the current
-- level to current level + 1
SELECT @current_level = @max_level
WHILE 1 = 1
BEGIN
SELECT @update_level = @current_level + 1
UPDATE #article_level_info
SET tree_level = @update_level
FROM #article_level_info
INNER JOIN sys.sql_dependencies d
ON #article_level_info.source_objid = d.object_id
INNER JOIN #article_level_info ali1
ON (d.referenced_major_id = ali1.source_objid
AND ali1.tree_level = @current_level
AND d.object_id <> d.referenced_major_id)
-- Terminate the algorithm if we cannot find any articles
-- depending on articles at the current level
IF @@ROWCOUNT = 0
GOTO PHASE1
IF @@ERROR <> 0
GOTO Failure
SELECT @current_level = @current_level + 1
-- Although there should not be any circular
-- dependencies among the articles, the following
-- check is performed to guarantee that
-- the algorithm will terminate even if there
-- is circular dependency among the articles
-- Note that with at least one node per level,
-- the current level can never exceed the total
-- number of articles (nodes) unless there is
-- circular dependency among the articles.
-- @limit is defined to be # of articles + 1
-- although @limit = # of articles - 1 will be
-- sufficient. This is to make absolutely sure that
-- the algorithm will never terminate too early
IF @current_level > @limit
GOTO PHASE1
END
PHASE1:
-- There may be interdependencies among articles
-- that haven't been included in the previous calculations so
-- we compute the proper order among these articles here.
SELECT @limit = @max_level - 9
SELECT @current_level = 0
WHILE 1 = 1
BEGIN
SELECT @update_level = @current_level + 1
UPDATE #article_level_info
SET tree_level = @update_level
FROM #article_level_info
INNER JOIN sys.sql_dependencies d
ON (#article_level_info.source_objid = d.object_id
AND #article_level_info.tree_level < @max_level)
INNER JOIN #article_level_info ali1
ON (d.referenced_major_id = ali1.source_objid
AND ali1.tree_level = @current_level
AND d.object_id <> d.referenced_major_id)
IF @@ROWCOUNT = 0
GOTO PHASE2
IF @@ERROR <> 0
GOTO Failure
SELECT @current_level = @current_level + 1
IF @current_level > @limit
GOTO PHASE2
END
PHASE2:
-- Select the articles out of #article_level_info
-- in ascending order of tree_level. This will give
-- the proper order in which articles can be created
-- without violating the internal dependencies among
-- the themselves. Note that this algorithm still allows
-- unresolved external references outside the publication.
-- All this algorithm can guarantee is that all articles will
-- be created successfully using the resulting order if
-- there is no dependent object outside the publication.
SELECT article
FROM #article_level_info
ORDER BY major_type ASC, tree_level ASC, processing_order ASC, nickname ASC
DROP TABLE #article_level_info
RETURN (0)
Failure:
DROP TABLE #article_level_info
RETURN (1)
No comments:
Post a Comment