How to resolve order of replicated articles so that dependent ones are done last?

Hi experts,

SQL Server 2008 R2

I am a beginner with replication and am trying to run merge replication on an entire database (tables, views, functions, SPs etc).

The snapshot generated OK, I created the subscription OK but cannot "propagate to the subscriber".

I suspect this is because it is trying to send, for want of a better word, the view definition before it has sent the underlying table(s).  There are several interdependent views and tables, in the database; how do I resolve this?  

Is it a question of several publications with different articles, run in a hierarchical order from the bottom up?  Is there a system stored proc that can help me find what object is dependent on which others?

Thanks in advance,

 Colin
LVL 3
colinspursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

la-tempestadCommented:
what is the SSMS>Replication>replication monitor interface says?

Regards
la
0
colinspursAuthor Commented:
0
colinspursAuthor Commented:
I worked out dependency relationships with recursive CTE query:

if exists (select * from sysobjects where name='CB_OBJECT_HIERARCHY' and xtype='U')
    DROP TABLE CB_OBJECT_HIERARCHY
go

;WITH VIEWS_CTE (depender_name, depender_type, dependee_name, dependee_type, object_level) AS
---- Anchor member definition
  (

   SELECT DISTINCT o.name as oname, o.type_desc as otype_desc, p.name as pname, p.type_desc as ptype_desc, 0 as object_level

        FROM sys.sql_dependencies d1
        INNER JOIN sys.objects o
              ON d1.object_id = o.object_id
        INNER JOIN sys.objects p
              ON d1.referenced_major_id = p.object_id
        LEFT JOIN sys.sql_dependencies d2
              ON o.object_id = d2.referenced_major_id
--        WHERE o.type_desc = 'VIEW'
--            AND p.type_desc = 'VIEW'
        WHERE o.type IN ('V', 'FN', 'P', 'IF', 'TF')
            AND p.type IN ('V', 'FN', 'P', 'IF', 'TF', 'U')
            AND d2.referenced_major_id IS NULL
            AND o.name NOT LIKE 'MSMerge%'
            AND p.name NOT LIKE 'MSMerge%'
 
      UNION ALL
        
        SELECT o.name, o.type_desc, p.name, p.type_desc, object_level +1
        FROM sys.sql_dependencies d1
        INNER JOIN sys.objects o
              ON d1.object_id = o.object_id
        INNER JOIN sys.objects p
              ON d1.referenced_major_id = p.object_id
          INNER JOIN VIEWS_CTE vcte
                ON o.name = vcte.dependee_name
--        WHERE o.type_desc = 'VIEW'
--            AND p.type_desc = 'VIEW'
        WHERE o.type IN ('V', 'FN', 'P', 'IF', 'TF')
            AND p.type IN ('V', 'FN', 'P', 'IF', 'TF', 'U')
            AND o.name NOT LIKE 'MSMerge%'
            AND p.name NOT LIKE 'MSMerge%'
  )
 
SELECT DISTINCT depender_name, depender_type, dependee_name, dependee_type, object_level
INTO CB_OBJECT_HIERARCHY
FROM VIEWS_CTE
ORDER BY object_level, depender_name, dependee_name
OPTION (MAXRECURSION 1000)
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinspursAuthor Commented:
I worked it out in the end. First dependencies have to be found, then use sp_changemergearticle to set processing order for replication.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.