Loss of data due to conflict

hi all,
i installed merge replication successfully,after that i tried to add a row from publisher with id 2004 (it is primary key and autogenerated column)and different other columns,same like that i inserted a row from subscriber with id as 2004 and different ot
her column.when i observed after merge agent is successfull only one row is replicated the other row is failed to replicate due to conflict.this causing loss of data.please advise what i have to do to get data from both sides.
thanks®ards,
reddy
Reddy,
with merge, if you have identity columns as the PK, you need to partition
according to publisher and subscriber ie each uses its own range. Before
initialization, the publisher PK is set to be "Identity Yes (Not for
Replication)" and SQL Server will manage the seeds on publisher and
subscriber and you can define when a new seed is allocated. In your case
this doesn't seem to be happening, presumably because it is a manual setup?
If this is so, you'll need to partition the identity values yourself. Here
is an article which should help you:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||paul,
thank you very much for your information.
but if i set different ranges both on publisher and subscriber the sequence will be broken.is there anyother way you would like to suggest.
thanks®ards
chandra
|||In merge. there is no other way to partition on one single PK-identity column and avoid identity conflicts, as this would mean the subscriber needs to be in contact at all times with the publisher (zero autonomy). This is possible in transactional with im
mediate updating subscribers, as the publisher itself controlls all identity values, even those on the subscriber.
As an alternative, you could make your PK 2 columns with one of them as the Site Identifier, while the other is an identity column. In this way duplicate identity values could be added and this wouldn't result in a conflict.
HTH,
Paul Ibison
|||paul,
thank you very much for your information.i go for second option that is pk 2 columns with one of them as site identifier.
i think it will works fine for my requirement.
thanks®ards
reddy

Loop through relation to set incremental number

I have a relation with two attributes acting as the primary key. Each key
is a foreign key to another table. The requirements have changed and now I
need to set the relation with a single auto increment primary key. The
existing relation has about 45k tuples.
As you might guess, if I add a new attribute with the increment on I get
errors because the new attribute can't contain NULL values. I have tried to
look at ways to loop through each tuple and set the new attribute equal to a
counter variable but the end result is the value is always one. (That is
the start of my counter).
Any thoughts and suggestions are greatly appreciated.
WBIf I understand your question properly...
Try the following...
Alter Table <TableName> Add <NewColumnName> Integer Identity Not Null
This should add a new column (Attribute) to the table (Relation) and
automatically populate it with non-null sequentially increasing unique
numeric values.
"WB" wrote:

> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
>|||"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
> I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
> to
> look at ways to loop through each tuple and set the new attribute equal to
> a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
--Starting with
create table t(c1 int not null, c2 int not null, c3 varchar(255),
constraint pk_t primary key (c1,c2))
--create
create table t_new(id int identity primary key, c1 int, c2 int, c3
varchar(555),
constraint ak_t unique(c1,c2))
insert t_new(c1,c2,c3) select c1,c2,c3 from t
exec sp_rename t,t_old
exec sp_rename t_new,t
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e5RcQ40IFHA.3628@.TK2MSFTNGP15.phx.gbl...
> insert t_new(c1,c2,c3) select c1,c2,c3 from t
Might want to add an ORDER BY clause to that one (if there's some
meaningful order?)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I failed to mention that the database is part of a merge replication. Will
your suggestions work across merge replication?
"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>sql