merge replication: primary key issue

Discussion:

(too old to reply)

saandii777

2010-07-12 17:32:57 UTC

Hello All,

We are facing a issue with merge replication.
Suppose I have a table customer (cid,cname) which we have published.
Note cid is primary key and it is not identity.

Initially there is one row in customer table (1,'Mohan')

After replication process
At publisher: one row is inserted with cid=2
At subscriber: one row is inserted with cid=2(allowed as working on its own
copy)

When we syncronize it throws an error as there are 2 rows with cid=2.
Note : we cannot set cid as identity.

Can anyone tell me how this issue is to be resolved.

Thanks
saandii777

saandii777

2010-07-12 19:29:54 UTC

Permalink

Hello all,

One of the solution I saw on internet is to add a column to table customer
having name of subscriber/publisher.

Thus defining cid and this column as primary key.

I know it will solve the purpose but I guess I am not in this position to
have table structure changed like this.

Someone plz help

saandii777

Post by saandii777
Hello All,
We are facing a issue with merge replication.
Suppose I have a table customer (cid,cname) which we have published.
Note cid is primary key and it is not identity.
Initially there is one row in customer table (1,'Mohan')
After replication process
At publisher: one row is inserted with cid=2
At subscriber: one row is inserted with cid=2(allowed as working on its own
copy)
When we syncronize it throws an error as there are 2 rows with cid=2.
Note : we cannot set cid as identity.
Can anyone tell me how this issue is to be resolved.
Thanks
saandii777

Dan

2010-07-13 09:51:07 UTC

Permalink

In that case you need to look into stopping conflicts from occurring, or
find a way to handle conflicts. For instance, MS have a large article for
SQL Server 2000 here: http://support.microsoft.com/kb/315514

Rather than pick one thing that you can't change, why not include details of
your system (SQL Server versions for instance) and what you can change? That
would make it easier for someone to suggest possible ideas.

Can you change the application? For instance, have the publisher use only
odd numbers, and the subscriber only use even numbers? Or use different
seeds for your id numbers, say starting at 1 for the publisher and 10000 for
the subscriber (which of course breaks when you hit 10000 ids inserted by
the publisher, but you haven't provided any information about the expected
numbers of rows inserted so I've just picked that arbitrarily, you would
need to select a value that would allow your system to scale as required).

If you search Google for articles about SQL Server merge replication
conflicts you'll find lots of information on what you could do.

Dan

Post by saandii777
Hello all,
One of the solution I saw on internet is to add a column to table customer
having name of subscriber/publisher.
Thus defining cid and this column as primary key.
I know it will solve the purpose but I guess I am not in this position to
have table structure changed like this.
Someone plz help
saandii777

saandii777

2010-07-13 14:07:11 UTC

Permalink

Thank you for the reply.

The information is very much helpful.
I can surely resolve this when primary key column is set to identity.

But in my case some of the tables cannot have primary key column set to
identity and I am really looking for a approach where this can still be
solved.

Note:
Publisher location: SQL SERVER 2008
Subscriber location: SQL SERVER COMPACT EDITION

thanks
saandii777

Post by Dan
In that case you need to look into stopping conflicts from occurring, or
find a way to handle conflicts. For instance, MS have a large article for
SQL Server 2000 here: http://support.microsoft.com/kb/315514
Rather than pick one thing that you can't change, why not include details of
your system (SQL Server versions for instance) and what you can change? That
would make it easier for someone to suggest possible ideas.
Can you change the application? For instance, have the publisher use only
odd numbers, and the subscriber only use even numbers? Or use different
seeds for your id numbers, say starting at 1 for the publisher and 10000 for
the subscriber (which of course breaks when you hit 10000 ids inserted by
the publisher, but you haven't provided any information about the expected
numbers of rows inserted so I've just picked that arbitrarily, you would
need to select a value that would allow your system to scale as required).
If you search Google for articles about SQL Server merge replication
conflicts you'll find lots of information on what you could do.
Dan

Dan

2010-07-13 14:31:39 UTC

Permalink

I already suggested an approach where you don't have identity, you need to
get your application to use primary key values that will not result in
conflicts. I'm assuming that your application is simply reading the highest
value from the cid column and adding 1? If so, change it - for instance, use
a separate table to hold the last value used and increment that, and start
the value off on the publisher at 1 and at the subscriber with a high number
like 100000.

Dan

Post by saandii777
Thank you for the reply.
The information is very much helpful.
I can surely resolve this when primary key column is set to identity.
But in my case some of the tables cannot have primary key column set to
identity and I am really looking for a approach where this can still be
solved.
Publisher location: SQL SERVER 2008
Subscriber location: SQL SERVER COMPACT EDITION
thanks
saandii777

Post by saandii777
Hello All,
We are facing a issue with merge replication.
Suppose I have a table customer (cid,cname) which we have published.
Note cid is primary key and it is not identity.
Initially there is one row in customer table (1,'Mohan')
After replication process
At publisher: one row is inserted with cid=2
At subscriber: one row is inserted with cid=2(allowed as working on
its
own
copy)
When we syncronize it throws an error as there are 2 rows with cid=2.
Note : we cannot set cid as identity.
Can anyone tell me how this issue is to be resolved.
Thanks
saandii777

saandii777

2010-07-13 19:05:38 UTC

Permalink

Thank you ver much.
Your suggestion has really solved the purpose.
We are actually planning to maintain a table with columns
(tbl_name,currentvalue,min,max)

With current value we know the position to insert and always with min and max
we know publisher/subscriber is inserting within the permisible range else
we update the range and then allow insert.

Thanks for the suggestion.
saandii777

Post by Dan
I already suggested an approach where you don't have identity, you need to
get your application to use primary key values that will not result in
conflicts. I'm assuming that your application is simply reading the highest
value from the cid column and adding 1? If so, change it - for instance, use
a separate table to hold the last value used and increment that, and start
the value off on the publisher at 1 and at the subscriber with a high number
like 100000.
Dan

Post by saandii777
Hello All,
We are facing a issue with merge replication.
Suppose I have a table customer (cid,cname) which we have published.
Note cid is primary key and it is not identity.
Initially there is one row in customer table (1,'Mohan')
After replication process
At publisher: one row is inserted with cid=2
At subscriber: one row is inserted with cid=2(allowed as working on
its
own
copy)
When we syncronize it throws an error as there are 2 rows with cid=2.
Note : we cannot set cid as identity.
Can anyone tell me how this issue is to be resolved.
Thanks
saandii777

masrath

2010-07-22 08:02:09 UTC

Permalink

Hello Saandhii
first the primary requsite for replication is the table must contain primary
key column then only replication task will be possible. create a table with
primary key column contain then do replication