Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert