May 8, 2012

sp_MScomputemergearticlescreationorder (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_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

Total Pageviews