Unanswered: How to handle identity Primary Keys with SQL Server and ActiveSync?
I have a pocket pc database application for mobile use. The original db is SQL Server and I used the ActiveSync tools to import the SQL database structure to my Pocket PC (which converted the db to a .cdb file) and keep the tables in sync.
I will have two or more users to begin with. The plan is that the users will connect to the SQL server through active sync to get the new data and also update the existing data through active sync. My problem is that one of the tables has an auto number identity primary key, therefore if user 1 enters records in the database numbers will automatically be assigned to the records which at some point will overlap with auto numbers for the records that the other user has entered in the same table, then when they connect to the server to, sync depending on which one first connected the other will overwrite data.
My guess is that since active sync is not using any replication technology the data will be lost or conflicts will occur.
My solution: Since I dont have any experience with Replication and synch I though that I would use an auto number field and concatenate the user ID to it to create a new value, which will be unique between all users after they sync.
Can you please let me know if there is a better way to handle this?
The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.