Merge replication, conflict resolution question...

Hi,
I'm trying to resolve a merge replication conflict problem, and I'm not sure
how to do it, additionally to this, I imagine that this is a very common
problem, so, some expert may point me on the right direction.
I have 3 tables that needs to be replicated, 1 publisher, 10 anonymous
subscribers that do a 'pull' and each subscriber synchronize automatically
once a day, a requirement is that all conflicts must be resolved without
human intervention.
These are the structures of the tables I'm using :
CREATE TABLE [dbo].[States] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Cities] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StateId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Properties] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[PublicationDate] [datetime] NOT NULL ,
[Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[Price] [money] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[States] WITH NOCHECK ADD
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD
CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[States] ADD
CONSTRAINT [DF_States_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
(
[Name]
) ON [PRIMARY]
ALTER TABLE [dbo].[Cities] ADD
CONSTRAINT [DF_Cities_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [IX_Cities] UNIQUE NONCLUSTERED
(
[Name]
) ON [PRIMARY]
ALTER TABLE [dbo].[Properties] ADD
CONSTRAINT [DF_Properties_Id] DEFAULT (newid()) FOR [Id]
ALTER TABLE [dbo].[Cities] ADD
CONSTRAINT [FK_Cities_States] FOREIGN KEY
(
[StateId]
) REFERENCES [dbo].[States] (
[Id]
)
ALTER TABLE [dbo].[Properties] ADD
CONSTRAINT [FK_Properties_Cities] FOREIGN KEY
(
[CityId]
) REFERENCES [dbo].[Cities] (
[Id]
)
Ok. the three tables needs to be merged and I want to resolve this problem :
Subscriber1 enter this data :
State [1, 'state1']
City [1, 'city1', 1]
Subscriber2 enter this data :
State [2, 'state1']
City [2, 'city2', 2]
Now subscriber1 synchronize and then subscriber2, and the data on the
publisher is :
State [1, 'state1']
City [1, 'city1', 1]
(the second state1 cant be inserted because of the unique contraint on the
name and the city2 cant be inserted because the fk does not exists)
(this is with the default resolver)
And I want this to be resolved as :
State [1, 'state1']
City [1, 'city1', 1]
City [2, 'city2', 1]
So, the questions are :
Can this be resolved with a custom stored procedure resolver ?
It seems that I need to run an stored procedure on the publisher just after
a subscriber upload his data, so the modified keys are properly changed at
the subscriber when the data back to him... obiously, for this to work I
need to remove the name's unique constraints, and handle this uniqueness
manually... can this solution be applied ?
I would really apressiate some help, thanks.
Paul.
Please any advice ?
"-ND-" <ndeath@.gmx.net> wrote in message
news:%23WwDRSvxEHA.1168@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to resolve a merge replication conflict problem, and I'm not
sure
> how to do it, additionally to this, I imagine that this is a very common
> problem, so, some expert may point me on the right direction.
> I have 3 tables that needs to be replicated, 1 publisher, 10 anonymous
> subscribers that do a 'pull' and each subscriber synchronize automatically
> once a day, a requirement is that all conflicts must be resolved without
> human intervention.
> These are the structures of the tables I'm using :
> CREATE TABLE [dbo].[States] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Cities] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [StateId] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Properties] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [PublicationDate] [datetime] NOT NULL ,
> [Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [CityId] [uniqueidentifier] NOT NULL ,
> [Price] [money] NOT NULL
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[States] WITH NOCHECK ADD
> CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD
> CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD
> CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[States] ADD
> CONSTRAINT [DF_States_Id] DEFAULT (newid()) FOR [Id],
> CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
> (
> [Name]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Cities] ADD
> CONSTRAINT [DF_Cities_Id] DEFAULT (newid()) FOR [Id],
> CONSTRAINT [IX_Cities] UNIQUE NONCLUSTERED
> (
> [Name]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Properties] ADD
> CONSTRAINT [DF_Properties_Id] DEFAULT (newid()) FOR [Id]
> ALTER TABLE [dbo].[Cities] ADD
> CONSTRAINT [FK_Cities_States] FOREIGN KEY
> (
> [StateId]
> ) REFERENCES [dbo].[States] (
> [Id]
> )
> ALTER TABLE [dbo].[Properties] ADD
> CONSTRAINT [FK_Properties_Cities] FOREIGN KEY
> (
> [CityId]
> ) REFERENCES [dbo].[Cities] (
> [Id]
> )
>
> Ok. the three tables needs to be merged and I want to resolve this problem
:
> Subscriber1 enter this data :
> State [1, 'state1']
> City [1, 'city1', 1]
> Subscriber2 enter this data :
> State [2, 'state1']
> City [2, 'city2', 2]
> Now subscriber1 synchronize and then subscriber2, and the data on the
> publisher is :
> State [1, 'state1']
> City [1, 'city1', 1]
> (the second state1 cant be inserted because of the unique contraint on the
> name and the city2 cant be inserted because the fk does not exists)
> (this is with the default resolver)
> And I want this to be resolved as :
> State [1, 'state1']
> City [1, 'city1', 1]
> City [2, 'city2', 1]
> So, the questions are :
> Can this be resolved with a custom stored procedure resolver ?
> It seems that I need to run an stored procedure on the publisher just
after
> a subscriber upload his data, so the modified keys are properly changed at
> the subscriber when the data back to him... obiously, for this to work I
> need to remove the name's unique constraints, and handle this uniqueness
> manually... can this solution be applied ?
> I would really apressiate some help, thanks.
> Paul.
>