partial table(records) synchronization issue RRS feed

  • Question

  • I had implemented a solution using the Sync Framework, to make partial table synchronization based on certain Id. Basically I am delivering data to the client based not only on who the client is but also what the account is (AccountId), so that we only send the records of the table the account should be able to see.

    Now, there is a problem described as follows:

    1. Suppose there are three tables, one of them being a join table (keeps links between the other two, saw Accounts_Tp, between table Accounts and table Tp).
    2. Now suppose at this point both db were synchronized successfully.
    3. Now, I add a record to table tp.
    4. I synchronize.
    5. Since tp is not associated to my account I am not going to receive this change in the client.
    6. Anchors are updated, saying that everything is in sync
    7. Now I link the previously added Tp to my account.
    8. I synchronize
    9. I will receive in the client the record but when inserting it will fail  by a foreign key violation, meaning, it would depend on the tp being there but the tp was never transferred before.

    If I create a mechanism to get rid of the anchors so that all of the data is brought to the client again I will experience long delays and a performance hit, since many records exist already in the client db. Not to speak about bandwidth usage. My db is 120+ tables, out of which 75 are being synchronized.

    I appreciate your input on this matter.

    C# + BizTalk2009 Developer
    • Edited by mape1082 Thursday, January 28, 2010 10:44 PM
    Wednesday, January 27, 2010 8:33 PM


  • Please do dummy updates on both recoreds and try a sync again. Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 28, 2010 5:53 PM

All replies

  • Please do dummy updates on both recoreds and try a sync again. Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 28, 2010 5:53 PM
  • Thanks, I am in the process of trying your suggestion.

    C# + BizTalk2009 Developer
    Tuesday, February 9, 2010 10:42 PM
  • Actually, you dont need 3 tables. You can create a foreign key for the two tables and establish that relationship.
    Another question is what providers are you using to sync? DbServerSyncProvider and SqlCeClientSyncProvider or SqlSyncProvider and SqlCeSyncProvider?

    If the former, check out: How to: Filter Rows and Columns using the join filter of the 2 tables.
    If the latter, then it is still possible using the join filters, but a little tricky or you can denormalize and add the primary tables filter column into the child table and use individual tables filters to get your subset data.

    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 11, 2010 7:40 AM
  • I had posted days ago a reply but for a reason is not showing up now.

    Sorry for late replay, was interrupted with other developments.

    To M. Zhou: I tried the dummy updates, but does not work:

    I sync table A, B, and A_B (all of them partially, meaning, only the records associated  to a given concept, in my case, account). 
    Add a record B1, which is not synced because is not part of the account
    Add record A1_B1 (to establish a link between record A1 and record B1)
    Dummy update record B1 (because we know this record has never been sent to the client)

    At this point, to the client, two changes would be transfered:
    Add record A1_B1
    Update record B1

    but what ends up happening is that the Sync Framework tries to update record B1 in the client, but it simply does not exist. Add record A1_B1 fails then, because of the same reason.

    Any thoughts?

    Is there a way to have the sync framework try to Insert if the Update caused no impact (0 record changed?)

    I am using SqlServer to Sql Server synchronization,  as well as SqlServer to Compact. The classes: Microsoft.Synchronization.Data.Server.DbServerSyncProvider, ClientSyncProvider and SqlCeClientSyncProvider


    C# + BizTalk2009 Developer
    Wednesday, March 10, 2010 8:44 PM
  • you can try modifying the Update stored procedure so that it does an insert instead of an update if the row does not exists. (i.e., do an update, if @@rowcount=0, do an insert)
    Thursday, March 11, 2010 1:07 AM
  • Thanks! Yesterday I had implemented something similar: used the event ApplyingChanges and for each datarow of each table in the dataset I checked for its existence in the db. If a record was not found, I change the DataRowState from Modified to Added.

    This occurs before the dataset is passed on to the framework routines that do the actual inserts/updates.

    I dont use stored procedures, since I am relying on the Adapter generated objects for the tables. Moreover, I am using compact edition too, which does not support stored procedures, nor it supports the  @@rowcount global variable.

    My solution works but is expensive. I'd change to your suggestion if I had means to let the application know that a particular update of a row returned 0 rows affected. The last contact I have with the data before it is written is the ApplyChanges event. After the actions have been taken, I have no means with the framework to check the mentioned condition.


    C# + BizTalk2009 Developer
    Thursday, March 11, 2010 5:19 PM
  • yes, that's a bit expensive. Have you tried checking the ApplyChangesFailed event instead to see if an update to a non-existing row in the client raises an error? If it does, then you only have to change the DataRowState for the failing record rather than checking all downloaded rows.
    Thursday, March 11, 2010 5:53 PM