Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScomputemergearticlescreationorder

  No additional text.


Syntax
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)

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash