none
SyncDirection Upload Only not working as expected. RRS feed

  • Question

  • I am syncing from SqlCe 3.5 mobile to Sql2005. I have a table with SyncDirection = SyncDirection.UploadOnly.

    I create a record on the device and when the Sync runs the record is inserted. All is well. However, updates to this record fail to sync.

    I believe the problem is with the Update command that is generated. In the designer, I specified to track updates by TimeStamp column.

    WHERE ([Id] = @Id) AND (@sync_force_write = 1 OR ([TimeStamp] <= @sync_last_received_anchor).

    If I change this simply to WHERE([Id] = @Id) it works.

    If I change this to WHERE ([Id] = @Id) AND (@sync_force_write = 1 OR ([TimeStamp] >= @sync_last_received_anchor). it works

    So my question is, why is the update looking at the anchor in the first place?

    If I change the sync direction to BiDirectional, the update succeeds.

     

     

     

     

    Friday, March 19, 2010 4:37 PM

Answers

  • here's a quick workaround:

    set the sync direction to bidirectional

    the before calling the sync:

     // Call SyncAgent.Synchronize() to initiate the synchronization process.
     // Synchronization only updates the local database, not your project's data source.
    LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
    
    //get instance of the remote provider 
    LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
    
    //set the selectincremental selects to null
    remoteProvider.TestSyncAdapter.SelectIncrementalInsertsCommand = null;
    remoteProvider.TestSyncAdapter.SelectIncrementalUpdatesCommand = null;
    remoteProvider.TestSyncAdapter.SelectIncrementalDeletesCommand = null;
    
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    

    this way you get to update the last received anchor without downloading any changes

    • Marked as answer by gs_ham Friday, March 26, 2010 1:36 AM
    • Marked as answer by gs_ham Friday, March 26, 2010 1:37 AM
    Thursday, March 25, 2010 10:13 PM
    Moderator
  • makes a sense now. reason for this is for uploadonly, the last received anchor at the client will not be updated ( this is part of downlaod phase ), hence the behavior you encountered here.

    there was a similar post some time back and we consider this as "by-designed" and we can consider to have DCRs for future releases. as a workaround, the user app can retrive the timestamp ( or the anchor value ) from the server for each sync and update it a the client side by calling the setTableAnchor() method to get it updated.

    thanks JuneT for the repro.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by L Zhou [MSFT]Editor Thursday, March 25, 2010 8:34 PM
    • Marked as answer by gs_ham Friday, March 26, 2010 1:36 AM
    Thursday, March 25, 2010 6:49 PM
    Moderator

All replies

  • the code generated by the sync designer under VS doesn't support bidirectional sync as defalt. please follow the steps in this link http://blogs.msdn.com/sync/archive/2008/06/16/extending-visual-studio-2008-sp1-sync-designer-to-support-bi-directional-synchronization.aspx to enable bidiretional syncs.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 23, 2010 9:56 PM
    Moderator
  • Thanks for your reply however that does not address the question I asked. I am trying to sync from client to server Upload Only, not BiDirectional.

    So, when a record is added on the client I want it inserted on the server. And when a record is updated on the client I want it updated on the server. As I wrote in my original question, Insert works fine but any updates only work if I modify the server side generated code for the update command to ignore the anchor.

    Thursday, March 25, 2010 3:10 AM
  • The reason to have this ([TimeStamp] <= @sync_last_received_anchor) in the where clause is to avoid updating any rows that were changed on the server ( i.e. the conflict case ).

    can you enable profile at the SqlServer to see what is the value of the @sync_last_received_anchor and also check the [TimeStamp] value for this particular row ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 25, 2010 6:11 AM
    Moderator
  • The reason to have this ([TimeStamp] <= @sync_last_received_anchor) in the where clause is to avoid updating any rows that were changed on the server ( i.e. the conflict case ).

    can you enable profile at the SqlServer to see what is the value of the @sync_last_received_anchor and also check the [TimeStamp] value for this particular row ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 25, 2010 6:12 AM
    Moderator
  • hi Yunwen,

    I was able to reproduce this behaviour following these steps:

    1. Use Local Database Cache Designer using timestamp as update column, new and incremental updates (there is no option for bidirectional or upload only in the designer)

    2. Two rows in the server with timestamp 0x0000000000000FA1 and 0x0000000000000FA5

    3. Rows get downloaded to SDF, last received anchor is set to 0x0000000000000FA5

    4. Change sync direction to UploadOnly

    5. Do an insert on the SDF and sync, sql profiler shows last received anchor as 0x0000000000000FA5, this works since inserts on server dont check for timestamp

    6. newly inserted row in server get's timestamp of 0x0000000000000FA6

    7. update the row on client again then sync,  sql profiler shows last received anchor as 0x0000000000000FA5 (we only upload so last received never changes), however timestamp of the same row which was previously uploaded and inserted to server in step 5/6 is  0x0000000000000FA6.

    8. Where condition is 0x0000000000000FA6 <= 0x0000000000000FA5, so it fails and no update happens.

    thanks

    junet

    Thursday, March 25, 2010 1:24 PM
    Moderator
  • makes a sense now. reason for this is for uploadonly, the last received anchor at the client will not be updated ( this is part of downlaod phase ), hence the behavior you encountered here.

    there was a similar post some time back and we consider this as "by-designed" and we can consider to have DCRs for future releases. as a workaround, the user app can retrive the timestamp ( or the anchor value ) from the server for each sync and update it a the client side by calling the setTableAnchor() method to get it updated.

    thanks JuneT for the repro.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by L Zhou [MSFT]Editor Thursday, March 25, 2010 8:34 PM
    • Marked as answer by gs_ham Friday, March 26, 2010 1:36 AM
    Thursday, March 25, 2010 6:49 PM
    Moderator
  • here's a quick workaround:

    set the sync direction to bidirectional

    the before calling the sync:

     // Call SyncAgent.Synchronize() to initiate the synchronization process.
     // Synchronization only updates the local database, not your project's data source.
    LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
    
    //get instance of the remote provider 
    LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
    
    //set the selectincremental selects to null
    remoteProvider.TestSyncAdapter.SelectIncrementalInsertsCommand = null;
    remoteProvider.TestSyncAdapter.SelectIncrementalUpdatesCommand = null;
    remoteProvider.TestSyncAdapter.SelectIncrementalDeletesCommand = null;
    
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    

    this way you get to update the last received anchor without downloading any changes

    • Marked as answer by gs_ham Friday, March 26, 2010 1:36 AM
    • Marked as answer by gs_ham Friday, March 26, 2010 1:37 AM
    Thursday, March 25, 2010 10:13 PM
    Moderator
  • Thanks JuneT and Yunwen for the workarounds.

    My current workaround was simply to override the generated update command in the partial method OnInitialize() and not include the  ([TimeStamp] <= @sync_last_received_anchor) piece of the where clause. This just forces any update on the client to update the server which is what I want for this particular table.

    I understand now that this is "by-design" and it makes sense. Hopefully in the future the Sync Designer wizard will allow more options to handle this and other situations and generate the code accordingly.

    JuneT, I like your workaround of setting the various commands to null. I was doing this for delete commands because we do not delete anything in this system but it seemed like a hack to me. Again, hopefully the designer will handle this as well and simply not generate delete commands if you do not specify how to handle deletes.

    Thanks again.

    Friday, March 26, 2010 1:36 AM
  • actually, in the designer, if you specify "none" either for "Compare updates using:"  or "Compare deletes using" for a table, then no SelectIncremental command is created for either of the Update or Delete.

    I used to edit the SQL statements as well, but it's just time consuming when you re-generate the sync using the designer and sometimes the developer forgets to update the designer generated code.

    So i figured to do these right before the sync (even adding filter clause: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry) before I make a call to Synchronize so its so obvious in the code what I'm overriding in the designer generated code.

    Friday, March 26, 2010 3:05 AM
    Moderator