merge replication: primary key issue
in [Replication]
|
From: saandii777 on 12 Jul 2010 13:32 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
From: saandii777 on 12 Jul 2010 15:29 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" wrote: > 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 >
From: Dan on 13 Jul 2010 05:51 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 "saandii777" <saandii777(a)discussions.microsoft.com> wrote in message news:065F9DB1-85BA-43C5-BBF5-8023ECD02EC0(a)microsoft.com... > 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" wrote: > >> 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 >>
From: saandii777 on 13 Jul 2010 10:07 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 "Dan" wrote: > 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 > > > "saandii777" <saandii777(a)discussions.microsoft.com> wrote in message > news:065F9DB1-85BA-43C5-BBF5-8023ECD02EC0(a)microsoft.com... > > 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" wrote: > > > >> 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 > >> > > > > . >
From: Dan on 13 Jul 2010 10:31
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 "saandii777" <saandii777(a)discussions.microsoft.com> wrote in message news:E4CBB797-6146-4CF8-BC56-986E619BC60E(a)microsoft.com... > 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 > > "Dan" wrote: > >> 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 >> >> >> "saandii777" <saandii777(a)discussions.microsoft.com> wrote in message >> news:065F9DB1-85BA-43C5-BBF5-8023ECD02EC0(a)microsoft.com... >> > 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" wrote: >> > >> >> 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 >> >> >> >> >> >> . >> |