Answered by:
Applying changes on client inserting instead of updating

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?- Moved by Liam Cavanagh - MSFTMicrosoft employee Thursday, January 21, 2010 7:41 PM (From:SyncFx - Microsoft Sync Framework Database Providers)
Thursday, July 2, 2009 1:56 PM
Answers
-
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 PMAnswerer
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.- Proposed as answer by L Zhou [MSFT]Editor Tuesday, July 21, 2009 2:34 PM
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,
PhilipFriday, 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 PMAnswerer -
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,
PhilipTuesday, 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 PMAnswerer -
Hello Jin
Thanks for the response that helped.
Regards,
PhilipThursday, January 7, 2010 9:26 AM