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

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup OR Join?

Does anyone have experience with comparing the performance in dealing with very large data sets to determine the presence of a record based on key fields in order to determine wheter to update or insert a record to a table? With another ETL tool, large lookups perform better with a join transformation component, rather than an actual lookup component, because of the large caching requirements.

In using a join, you simply base the condition on the result of the join, as you can with an SSIS lookup component 'erroring' out. Some tools also have an actual 'upsert' component that helps in achieving such a scenario.

Thoughts?Joe,
During the research for this article: http://www.sqlis.com/default.aspx?311 I was comparing the 2 methods. LOOKUP performed significantly better although it wasn't on a big dataset.

Also I have a case study coming out on my blog imminently (it has to go through manager approval first) in which the 2 methods are compared on a very very large dataset (millions of rows). The performance increase when using LOOKUPs was breathtaking - I can't give details as I'm under unofficial NDA at the moment...but look out for it in the next few days on http://blogs.conchango.com/jamiethomson.

-Jamie|||I have been using a SQL join and creating a new column with either a 'U' for update or 'I' for insert. I am comparing a small number of rows against a large dataset. I am interested in the results of your case study, Jamie - I will watch for it.
-Evan Black