Hi,

My app allows a user to add a list item, which gets assigned an ID, which is
referenced in tables instead of its name. How do I merge same items added by
Publisher and Subscribers into a single one and propagate the ID change to
all affected tables at the applicable sites?

Thanks in advance.

Thanks Paul,

I should have said I meant a merge replication. I thought that subscriber's
triggers are replicated from the publisher i.e.identical. If I got you right
the trigger must be 'instead of' otherwise FK constraints would interfere.
But to fire a trigger the check still needs to be done i.e. the only
solution (apart from obviously not having a unique constraint on item names
and using ID ranges) is to find a duplicate item and replace its ID in all
tables where it occurs with another and delete it afterwards either on the
publisher or the subscriber (he's the only one who uses it). It's probably
got to be a periodic maintenance job as I don't know how to run it
automatically during/after the synchronization event. It's a huge pain
anyway.

Dmitry Shulukin

[quoted text, click to view]
have Transactional Replication with Immediate Updating Subscribers, you
could allow an insert the list item on the subscriber only through a stored
procedure. The procedure looks at the publisher to see if the item exists -
if it does it simply returns the ID, if not it enters it on the subscriber.
This type of replication initiates a 2PC distributed transaction to enter
the record on publisher and subscriber and could return the new ID. This way
you are ensured not to have duplicates. This method relies on connectivity.
If this is not the case then you would have to use queues or merge
replication and there is no out of the box solution for what you want. There
are many scripts out there to remove duplicates based on group by and max
functions, but in your case the delete needs to be propagated as an update
of the primary key value, which cascades on the subscriber using the FK
constraint. Unfortunately the delete on the publisher will replicate as a
delete on the subscriber also. One posibility in this case might be to edit
the delete trigger on the subscriber to in certain cases do a delete
followed by an update instead. This is treading on slightly shaky ground in
terms of MS support and to be sure it is acceptable (seems feasible) you'd
be best to get in touch with a MS representative.
[quoted text, click to view]

Thanks Jim,

I guess the only way is to allow duplicate values to co-exist for while and
to reassign and clean them up during periodic maintenance. It's certainly
a bit of an ugly solution but anyway MS adds a range of its own columns
to yours (for a merge replication), limits you to 6000 bytes and requires
field lists in every INSERT - it's already ugly. So what's the difference?

Dmitry Shulukin



[quoted text, click to view]