duplicate key insert failure (again)

I'm reposting this because no one responded to my Dec 12 post. There
has to be a way of dealing with upload insert failures. Could someone
please point me in the right direction?
I have one article giving me problems in my topology which involves
multiple Sql Server 2005 Express subscribers to a Sql Server 2005 merge
publication. The article implements an open schema in a many-to-many
relationship with a composite primary key to disallow duplicate
properties...
TABLE seed_properties
seedID int not null
propertyID int not null
value varchar(50) null
rowguid
What I want to happen, when the same property is created at multiple
push subscribers, is that the conflict resolver would choose the one
uploaded to the publisher first and delete the others, replacing them
with the winning property.
What's actually happening is the following...
During subscriber upload the losing subscribers' inserts are failing
with a duplicate key violation, but the subscribers' failing inserts
aren't being removed. So when the publisher attempts to propogate the
winning inserts they fail with another duplicate key violation because
the subscriber didn't delete it's failed inserts.
I WANT the subscriber to delete its failed inserts, but it won't. No
matter what I do in the conflict resolver dialog, the losing inserts
don't get deleted, and so they reappear the next time synchronization
occurs. I've tried all applicable COM conflict resolvers and even my
own custom BusinessLogicModule with no luck.
If I switch it to a pull subscription my BusinessLogicModule may be
able to delete the offending records through an ADO connection to
(local). But before I attempt that, I want to be sure I'm not missing
anything. Am I missing anything? =)
You need to set the compensate_for_errors setting to false. This will kick
back the subscriber inserts and have them overwritten by the publisher
change.
However, you also need to add a datetime column to your table and use the
datetime resolver so the first row in will win. I am not exactly sure how
your conflict handling will work as I am not totally sure what you are
trying to accomplish.
Are you trying to have multiple identical pk occuring on multiple
subscribers between the same sink resolve so the first one in wins for all
subscribers?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sprucely" <timberdig@.gmail.com> wrote in message
news:1166471620.672723.82420@.f1g2000cwa.googlegrou ps.com...
> I'm reposting this because no one responded to my Dec 12 post. There
> has to be a way of dealing with upload insert failures. Could someone
> please point me in the right direction?
> --
> I have one article giving me problems in my topology which involves
> multiple Sql Server 2005 Express subscribers to a Sql Server 2005 merge
> publication. The article implements an open schema in a many-to-many
> relationship with a composite primary key to disallow duplicate
> properties...
> TABLE seed_properties
> seedID int not null
> propertyID int not null
> value varchar(50) null
> rowguid
> What I want to happen, when the same property is created at multiple
> push subscribers, is that the conflict resolver would choose the one
> uploaded to the publisher first and delete the others, replacing them
> with the winning property.
> What's actually happening is the following...
> During subscriber upload the losing subscribers' inserts are failing
> with a duplicate key violation, but the subscribers' failing inserts
> aren't being removed. So when the publisher attempts to propogate the
> winning inserts they fail with another duplicate key violation because
> the subscriber didn't delete it's failed inserts.
> I WANT the subscriber to delete its failed inserts, but it won't. No
> matter what I do in the conflict resolver dialog, the losing inserts
> don't get deleted, and so they reappear the next time synchronization
> occurs. I've tried all applicable COM conflict resolvers and even my
> own custom BusinessLogicModule with no luck.
> If I switch it to a pull subscription my BusinessLogicModule may be
> able to delete the offending records through an ADO connection to
> (local). But before I attempt that, I want to be sure I'm not missing
> anything. Am I missing anything? =)
>