Setting filterClause in sync tables avoids synchronizing deleted records RRS feed

  • Question

  • Hi, 

      When setting the FilterClause in sync tables I´m failing to synchronize the records that where deleted in the server by someone else. This causes a synchronization failure on my local database. The weird thing is that all those filters return ids that are primary keys and the failure happens only for some filtered tables ,not for all of them.

    I will appreciate any hint :)




    Thursday, July 14, 2011 8:33 PM


All replies

  • can you post a sample filter clause? are you filtering based on another table?
    Thursday, July 14, 2011 11:59 PM
  • Hi, 

      My filter is defined as:  

      [side].id_person in (select Get_AllVisibleContacts.id_person from Get_AllVisibleContacts(@idPerson)) 


       Get_AllVisibleContacts gets the id of the user that is going to synchronize, and returns the ids of the contacts that he is able to see accoding to a given role. Other thing that perhaps gives you a hint is that Get_allVisibleContacts returns these values traversing BASE tables, not SIDE ones. Perhaps this is the problem since when i remove a record it is being removed from the BASE table. Then,  Get_AllVisibleContactsr is not going to return this id anymore and thus, the id of the deleted record in the SIDE table is not taken into account for the next synchronization....Does it make sense? If this is the case, how can i change my filter in order to be able to synchronize deleted records?


    Thanks in advance, 





    Friday, July 15, 2011 12:46 PM
  • your assumption is right.

    change Get_AllVisibleContactsto look at the side / tracking table instead.

    Friday, July 15, 2011 12:57 PM
  • Hi JuneT,

      First of all, thanks for the fast response. Changing Get_AllVisibleContacts is a little bit risky since this function is being used in many places in the application but what about changing the filter to something like this:


     MyTable.FilterClause = ([side].id_person in (select Get_AllVisibleContacts.id_person from Get_AllVisibleContacts(@idPerson))) OR  ([side].sync_row_is_tombstone  = 1)




    Friday, July 15, 2011 1:12 PM
  • if am not mistaken, the above filter will bring down all deleted rows and not just the one that's visible to the client.
    Friday, July 15, 2011 2:27 PM
  • yeah, you are right but what is the effect of trying to synchronize a deleted row that the client doesn´t have in his local data base? I suppose that the synchronizer just ignores it, doesn´t it?
    Friday, July 15, 2011 2:31 PM
  • if the amount of deletes is small, it should be fine. my comment is more around bringing down data to the client that would simply be discarded.

    Friday, July 15, 2011 2:40 PM
  • Hi,  

     Is there some other condition that i can add to the [side].sync_row_is_tombstone  = 1  in order to limit the amount of deletes that  the synchronizer should take into account? somethink like a timestamp parameter that i can take from tracking table?


    thanks in advance, 


    Wednesday, July 20, 2011 12:24 PM
  • the timestamp checking for incremental changes is already done by Sync Framework. your filter clause will be concatenated as part of a bigger WHERE clause. if you dont have that many deletes, you wont take that big performance hit.

    besides, for deleted rows, only the columns from the tracking table are sent as the original column has already been deleted and the PK is the only information required to remove the row on the destination.

    Wednesday, July 20, 2011 1:43 PM