none
Problem with deleted records not syncing RRS feed

  • Question

  • I am having a problem with record deletions and need to better understand how the sync framework processes them. My problem occurs in the following test case:

    • I insert, update, and delete records in my client database
    • I sync my client database to upload these changes to my central database
    • I delete my client database and recreate it
    • I sync my client database to my central database to populate it

    When I do this test, all of the data that should be in my client database is there. Unfortunately, the records that I deleted are also there.

    If I look at the client side tracking table following the deletions, it appears to be correct. I have a custom tombstone table that I populate with deleted records and it is also correct. However, if I find this record in the tracking table in the central database, it is not marked as deleted. So the deletions are not being propogated to the central database (I thought about possible referential integrity constraints, but we haven't enforced those in the database even though they are implied).

    I see two possibilities: either the deletions are not being select by my "selectchanges" stored procedure or they are not being propogated correctly due to an error in one the other stored procedures. I am using a semi-custom framework that I basically adapted from the framework provision by the sync framework (the stored procedures are the same except I have custom "selectchanges" stored procedures for row level filtering). I am pretty certain I did something wrong (I recall that this worked in my earlier prototype), but I don't know where to start looking. So I will do the next best thing and ask questions.

    First the standard "XXX_selectchanges" stored procedure has three input parameters:

    @sync_min_timestamp BigInt,
    @sync_scope_local_id Int,
    @sync_scope_restore_count Int
    

    Where do these values come from? I am particularly interested in where the value for "@sync_min_timestamp" comes from since that is used in the "Where" clause of the selection. I have an idea where this value may come from, but it would be helpful to know for certain.

    Second, when syncing a deletion to the central database, what stored procedures are called and what is the order they are called in? I would expect that at some point the client record has to be deleted from the table in the central database and the tracking table has to be updated (and probably in that order). Is there anything else involved in this transaction?

    Friday, December 17, 2010 1:39 PM

Answers

  • I figured out the problem yesterday and, as expected, it was "self inflicted".

    Our application uses row level filtering to "selectchanges", but the basic filtering value, a Site ID, is not present in all tables that use it for filtering. So what I did was add "Inner Join" clauses to the Select statement in in the selectchanges stored procedures. Of course, therein lies the problem. This works fine for inserts and updates, but when the record has been deleted the inner joins fails and nothing is selected.

    I see two possible solutions to my problem. First, I could add the Site ID as a column of my tracking table and populate the value from the insert trigger (it will not change). That would entirely eliminate the need for joins in the selectchanges stored procedures. The second alternative would be to use a Union statement to look for deletions.

    I don't know which way I will go; I see potential problems with either one. Either way, the sync framework is working as expected and at least now I better understand what this aspect of the framework is doing.

    • Marked as answer by PuzzledBuckeye Tuesday, December 21, 2010 1:00 PM
    Tuesday, December 21, 2010 1:00 PM

All replies

  • Looks like the case above involves some database deletion step.

    To help figuring problem out, could you try a simple delete on the client and see whether the delete is sync to central database. When delete happens, a tombstone row should be enumerated and a delete on the data should happen on the server, the tombstone row will also be inserted on the server side as well.

    Tuesday, December 21, 2010 12:23 AM
    Answerer
  • I figured out the problem yesterday and, as expected, it was "self inflicted".

    Our application uses row level filtering to "selectchanges", but the basic filtering value, a Site ID, is not present in all tables that use it for filtering. So what I did was add "Inner Join" clauses to the Select statement in in the selectchanges stored procedures. Of course, therein lies the problem. This works fine for inserts and updates, but when the record has been deleted the inner joins fails and nothing is selected.

    I see two possible solutions to my problem. First, I could add the Site ID as a column of my tracking table and populate the value from the insert trigger (it will not change). That would entirely eliminate the need for joins in the selectchanges stored procedures. The second alternative would be to use a Union statement to look for deletions.

    I don't know which way I will go; I see potential problems with either one. Either way, the sync framework is working as expected and at least now I better understand what this aspect of the framework is doing.

    • Marked as answer by PuzzledBuckeye Tuesday, December 21, 2010 1:00 PM
    Tuesday, December 21, 2010 1:00 PM