locked
Applying changes on client inserting instead of updating RRS feed

  • Question

  • Not having much luck with sync services so far. Everytime I think I've cracked it, another problem comes along to take its place.

    My current problem is that after I update the server with changes from the client; changes are made on the server and then downloaded to the client, but for some reason instead of updating the Primary Key row on the client with the new values, the sync process is trying to insert a duplicate Primary Key row with the changes (instead of updating the existing PK row with the changes from the server).

    If I handle the ChangeFailed event on the client, I can see the old and new values:

    Values in e.Conflict.ClientChange.Columns:

    IncidentUID= 882178d1-f576-42fd-affa-ca617dfe239b (Primary Key)
    DateCreated= 02/07/09 13:39:38
    DateIssued=
    DateDue= 03/07/09 02:39:38
    DateUpdated= 02/07/09 13:39:38

    Values in e.Conflict.ServerChange.Columns:

    IncidentUID= 882178d1-f576-42fd-affa-ca617dfe239b (Primary Key)
    DateCreated= 02/07/09 13:39:38
    DateIssued= 02/07/09 14:39:53 *** this field is changed ***
    DateDue= 03/07/09 02:39:53 *** this field is changed ***
    DateUpdated= 02/07/09 13:39:53 *** this field is changed ***

    I've set the SqlCeClientSyncProvider.ConflictResolver.StoreErrorAction to ResolveAction.ServerWins (which i thought would mean than the existing row would be overwritten but I keep getting a ChangeFailed event being raised despite this being set).

    The error message is "+        [System.Data.SqlServerCe.SqlCeException]    {"A duplicate value cannot be inserted into a unique index. [ Table name = Incidents,Constraint name = PK_Incidents ]"}    System.Data.SqlServerCe.SqlCeException:

    I thought sync services would check somewhere for existing PK before deciding to update/insert? I'm not sure why it is trying to insert a new row when it should be updating it instead?
    Thursday, July 2, 2009 1:56 PM

Answers

All replies

  • Seems you are getting the "loop back" problem, .e.g. the changes sent from a client was sent back to itself during the same sync ( bidirectional ).

    can you check you selectIncrementalInsert and selectIncrementalUpate queries at the server side ?

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 6, 2009 7:25 AM
  • Hello,

    We are getting the same 'loop back' problem.  Using SQL Server 2005 and  SqlCeClientSyncProvider.

    Running SQL Profiler on the selectIncrementalInsert SQL I can see it returns the newly inserted record by the client back to it again.
    The SelectIncrementalUpate doesn't return any records.

    I would have thought that the selectIncrementalInsert returns nothing and the selectIncrementalUpate one record in order to update the CreationDate and LastEditDate on the client.

    What is the solution to this problem? 

    Regards,
    Philip
    Friday, December 11, 2009 8:16 AM
  • Do you also track the Owner does CREATE and UPDATE for each data row?
    If so then you could add another piece of information in the where clause such as CreateOwner <> 'abc'?
    Otherwise, you may have the loop back problems as Yunwen indicated.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 14, 2009 6:29 PM
    Answerer
  • Hello Leo,

    In fact we are not tracking the Owner, that makes sense.  Shouldn't the CreateOwner field be generated together with the CreationDate and LastEditDate on the DB and code or is it perhaps a known issue?

    I am sure adding this field will resolve this problem but I would have thought this was supposed to work out of the box for bidirectional synchronization or am I missing something?

    Regards,
    Philip
    Tuesday, December 15, 2009 5:57 AM
  • Depending on your scenario, some schemas may be needed but some may not.
    Refer to  http://msdn.microsoft.com/en-us/library/cc305973(SQL.105).aspx  for more details.
    So for offline providers, we are not provisioning these automatically.

    • Marked as answer by Sean_Kelley Friday, January 8, 2010 1:18 AM
    Thursday, December 17, 2009 10:27 PM
    Answerer
  • Hello Jin

    Thanks for the response that helped.

    Regards,
    Philip
    Thursday, January 7, 2010 9:26 AM