Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SqlSyncScopeProvisioning - Problem with complex filter RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

           I am working on the Sync (Peer-to-Peer) between SQL Server 2005 databases. I am facing problem for sync with filter. Following is the situation :

    My database contains Practice, Address, Patient, Doctor tables as :

    Practice table's columns : PracticeUid, Name

    Address table's columns : AddressUid, City, State, Zip, Country, .....

    Patient table's columns : PatinetUid, FirstName, LastName, ............, AddressUid, PracticeUid

    Doctor table's columns : DoctorUid, ListName, Specialty, ........., AddressUid, PracticeUid

    I have created scope with Practice, Address & Patient tables with the filters :

    Practice => with PracticeUid = 'practice1Uid'

    Address => with AddressUid IN (SELECT AddressUid FROM Patient WHERE PracticeUid = 'practice1Uid') [Note : I want to sync only the Patient's Addresses and not of the Doctors.]

    Patient => with PracticeUid = 'practiceUid'.

    I am trying to sync but having trouble with the Address entry.

    Here is the problem with steps :

    1.) I created a new address (new entry in the Address table) in Peer1 database

    2.) Initiated the sync with Peer2 database.

    3.) Newly created address dint came over to the Peer2 (because it was not referenced to any Patient).

    4.) I created a Patient (new entry in the Patient table) in Peer1 database and referenced the address that was created before sync.

    5.) Initiated the sync with Peer2 database.

    6.) Newly created patient dint came over to Peer2. Reason is : 'The INSERT statement conflicted with the FOREIGN KEY constraint'. I checked the statistics and found that only the Patient entry was sent to the Peer2 but not the Address entry that is referenced by this Patient entry.

    7.) Then I updated the Address entry and initiated sync again.

    8.) This time I see two changes were uploaded. But I see same thing with the Patient entry i.e.  'The INSERT statement conflicted with the FOREIGN KEY constraint'. And the Update of the Address failed with the conflict 'LocalCleanedupDeleteRemoteUpdate'.

     

    Here I was expecting the Address entry to come over in the 2nd sync cycle (Step-5), but it dint so I tried Step-7 but no luck. The same situation is with other tables too in my database, so can you please tell me what I am supposed to do to handle this ?

    Please help.

     

    -Ajinath

    Wednesday, June 2, 2010 4:58 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    step 3 is an expected behaviour because of the IN clause specified in you filter.

    step 6's not bringing down the address i think is also an expected behaviour since Sync Fx has gone past the "timestamp" in step 3, so step 7 is the right approach: simply touch that row again to move its "timestamp"

    for step 8, am assuming your sync is processing the Patient table before the Address table which causes the Foreign Key error. Can you reorder your table in the scope definition so that Address table is added first before the Patient table?

    I suspect the 'LocalCleanedupDeleteRemoteUpdate' error is because Sync Fx is trying to update a row that does exists since it wasnt synched before (in step 3). This will actually be fired as a conflict and you can handle it by specifying RetryWithForceWrite action.

     

    Wednesday, June 2, 2010 12:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    step 6's not bringing down the address i think is also an expected behaviour since Sync Fx has gone past the "timestamp" in step 3, so step 7 is the right approach: simply touch that row again to move its "timestamp"

    for step 8, am assuming your sync is processing the Patient table before the Address table which causes the Foreign Key error. Can you reorder your table in the scope definition so that Address table is added first before the Patient table?

    I suspect the 'LocalCleanedupDeleteRemoteUpdate' error is because Sync Fx is trying to update a row that does exists since it wasnt synched before (in step 3). This will actually be fired as a conflict and you can handle it by specifying RetryWithForceWrite action.

     

    Thanks very much Junet for your valuable reply.

    To get the Address entry synced in Step-5/6, what needs to be done? Is there any way? Because waiting for update of the Address to Sync the Patient & Address is not going to work in my project. So please please give me some solution.

    I did added Address table before Patient table in the scope but still same behaviour. like :

    scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Address", (System.Data.SqlClient.SqlConnection)provider.Connection));
    scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Patient", (System.Data.SqlClient.SqlConnection)provider.Connection));      Is there anything else that needs to be done in order to make sure the sequence of tables in the Scope?

    Checking for the ConflictType 'LocalCleanedupDeleteRemoteUpdate' and setting the Action to 'RetryWithForceWrite' inserted the Address entry.

     

    Please help, I am running out of my timelines :(.

     

    -Ajinath
     

    Wednesday, June 2, 2010 1:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In step 4 of your scenario, is it possible you could also update the address entry right after adding the patient entry? This should get the address entry in step 5-6.

     

    Thanks,

    Ann Tang
    Friday, June 4, 2010 6:03 PM