Sorry for repost, but there was a problem with my MSDN posting alias.

I would appreciate some advice on generating a PK and/or synchronizing data
between two (maybe three) locations.

Simplified scenario.
Using SQL Server 2005 (could got to 2008 if needed).

Client has two locations (central and remote).
Item record would containseven fields - six int fields (id's from component
tables) and a seventh field that would be the PK.

Most of the time the PK would be generated at central, however, should the
connection be lost, remote must be able to generate a PK also and then when
connection is restored, any new PK generated by remote must be added to
central.

The PK needs to be the same for the same combination of the six fields, no
matter which server generated it, in case both servers happened to create a
record with the same six fields. Therefore using a GUID would not work.

Since the PK will be used by another system (Great Plains), it's length is
limited to 30 characters.

I am hoping for some algorathym that would combine the six column values
and always generate the same value from the same six values. Something like
a hash code. However, I am limited to 30 characters and I do believe a hash
code is 64.

Another idea is to write routines to do the 'replication', instead of using
SQL replication.
Then add all new rows in 'remote' via a stored procedure. If the sp can't
connect to 'central' it would generate it's own PK and also record that in
another table. Then when connectivity is restored, my replication routine
would check for duplicate rows and if necessary, update the PK in 'remote'
with the PK from 'central'.
Aside from the complexity, the problem with that is I don't know if Great
Plains can use cascade update to update its' detail tables that would be
referencing my PK as an FK.


--

AG
Email: discussATadhdataDOTcom