Re: Sync/Merge local sql compact databases to single global database

Tech-Archive recommends: Fix windows errors by optimizing your registry

One approach is to create a unique ID for each device. The PK could then be
constructed from this number (like 22) and a Identity value. When merged
with the host database you have the advantage of a unique number and knowing
from which device it was derived.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Chris" <morleyc@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9913000-0E41-40DF-8FC8-B81421D49A0D@xxxxxxxxxxxxxxxx
Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:

CREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);

CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);

CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);

The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID
column to a unique field (as no doubt lots of people will have 1, 2, 3 in
their local databases and the global database must have a unique ID), and
then transfer this changed ID back to the local database? Furthermore, if
it changes the Sessions.ID column during the merge it will also need to
update the SessionDetail.SessionID foreign key to maintain referential
integrity, is this also handled?

So my question is, how much of this sync and data merge is automated and
are there any good examples or pointers for my scenario? I cannot
reasonably use a GUID for Session.ID as there will be lots of
SessionDetail entries and the size would be far too much.

Many thanks,

Chris


.

Relevant Pages

  • Re: Altering columns...getting complicated...
    ... Unless there is a constraint, in which case I have to drop the constraint, alter the field, add the constraint back in. ... What I'm thinking is that I should dump all of the indexes and primary keys ... However, this database was "created" by using the Access upsizing wizard, so ... I don't know all the primary key names, constraint names, etc. ...
    (microsoft.public.sqlserver.msde)
  • replacing sp_MSinsTable1
    ... CREATE TABLE [Table1] ( ... CONSTRAINT PRIMARY KEY CLUSTERED ... The consolidated database looks like: ...
    (microsoft.public.sqlserver.replication)
  • Sync/Merge local sql compact databases to single global database
    ... for one of the tables i have had to use auto incrementing integer which works fine for the local database but i would like to merge all of the users databases into a global database. ... ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ... CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES Players ... how will the sync capabilities of compact SQL handle the sessions table? ...
    (microsoft.public.sqlserver.ce)
  • RE: Problems saving data to SQL database, timeout expired
    ... Thank you for having a look at the database structure. ... -- Installing the "event" database. ... ADD CONSTRAINT PK_AlarmState ... PRIMARY KEY ...
    (microsoft.public.sqlserver.server)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)