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