locked
Change the order of articles downloaded to subscribers. RRS feed

  • Question

  • Hi,

    I have a publication with table T1 referncing T2 with a foreign key relationship. However on the subscriber side T1 is getting downloaded first and then T2. Is there any way that I can change the order by which T2 is downlloade first and then T1. I tried sp_changemergearticle @processing order . But on the subscriber side I am still seeing table T1 downloaded first.

    Thursday, October 14, 2010 7:32 AM

Answers

  • As explaned by padmabhushanav you can use sp_changemergearticle to change value of @processing_order property on article. Agent internale order article which haven't this value set to something. Other articles are sort according value set for this property. 

    This is valid for SQL 2008 (and i think for SQL 2005).

    I'm using this for articles with somw views, sps and functions because merge agent sometimes don't order them in right way.

    • Marked as answer by Atish Agrawal Tuesday, November 2, 2010 4:18 AM
    Sunday, October 31, 2010 7:01 PM

All replies

  • The default processing_order is 0 for all articles, If you need to give order, keep 0 to all primary tables and 1 to all related child tables.

    Bhushan

    Thursday, October 14, 2010 9:11 AM
  • Hi Bhushan,

    Thanks for your reply. Actually I also wish to know what should I do when there are three tables T1, T2,T3 with T3 referencing T2 and T2 referencing T1. I tried giving procesing order of T1 as 1 , T2 as 2 and T3 as 3. But still some random behaviour of download of these tables is happening. Do I need to give some other values to this?

     

    Thanks,

    Atish

    Thursday, October 14, 2010 10:50 AM
  • I am curious as to why this is a problem for you. SQL Server will order the articles according to dri and put them in place.

    in SQL 2000 sysdepends could be wrong and you might run into problems applying the snapshot.

    Are you getting an error - or is there a particular problem you are trying to avoid?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, October 14, 2010 10:58 AM
    Answerer
  • Hi Hirary,

     

    I am trying to sync the publication with SQL CE as subscriber. To have intermediate looks i have created a log on the subscriber side which gives the order in which the tables are applied to the client side. In spite of what processing order I give , i see tables getting downloaded in a different order. Suppose in above eg if T3 is downloaded first. Now since T2 has not yet arrived on subscriber side, it fails and is put on retry queue. Now T1 gets downloaded, then T2 and now T3 is again downloaded thus resulting in correct insertion. However you can see where the problem is. T3 is downloaded twice. So I want to change the download order for the tables on SQL Server side since SQLCE does not support "NOT FOR REPLICATION" constraint.

    One this to note is that this is the behaviour I am seeing while initializing subscription. Initialization of subscription takes articles from snapshot. So I want to know whether there is a different behaviour between downloading from snapshot and SQL Server merger article processing order. If so, How can I control the order of articles downloaded from snapshot? As you are aware that the bulk copy only happens via snapshot so it is not affordable to allow T3 to be downloaded twice everytime.

     

    Thanks,

    Atish

    Thursday, October 14, 2010 11:24 AM
  • Hi Hilary,

     

    Any update on it Please? To rephrase my question once again, I want to change the order of articles present in a snapshot. If it is not possible, I am also comfortable with knowing a way to create a snapshot in a way so that only desired order of article downloads happen.

     

    Thanks,

    Atish

    Monday, October 18, 2010 4:57 AM
  • Hi,

     

    Can anyone help me to change the download order of articles from snapshot.. Please convey me if this is not possible.

     

    Thanks,

    Atish

    Monday, October 25, 2010 1:39 PM
  • As explaned by padmabhushanav you can use sp_changemergearticle to change value of @processing_order property on article. Agent internale order article which haven't this value set to something. Other articles are sort according value set for this property. 

    This is valid for SQL 2008 (and i think for SQL 2005).

    I'm using this for articles with somw views, sps and functions because merge agent sometimes don't order them in right way.

    • Marked as answer by Atish Agrawal Tuesday, November 2, 2010 4:18 AM
    Sunday, October 31, 2010 7:01 PM
  • Thank you Zelijko. :)
    Tuesday, November 2, 2010 4:18 AM