Replication Stops at distributor in some cases

Hi,
I recently had a problem with a merge replication publication. In
some cases when data is changed on a subscriber, the change gets back
to the publisher but does not replicate from the publisher to the rest
of the subscribers. I checked the conflict viewer and there are no
conflicts. I am running SQL Server 2000 and I was wondering if anyone
is familiar with this problem.
Thanks,
Paul
Are you using any sort of filtering? This might account for this behavior.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408091019.79d49cf2@.posting.google.c om...
> Hi,
> I recently had a problem with a merge replication publication. In
> some cases when data is changed on a subscriber, the change gets back
> to the publisher but does not replicate from the publisher to the rest
> of the subscribers. I checked the conflict viewer and there are no
> conflicts. I am running SQL Server 2000 and I was wondering if anyone
> is familiar with this problem.
> Thanks,
> Paul
|||I do have some row filters on but none that comes into play in this
particular situation.
|||Can you give us some more details about this topology? How many subscribers,
etc.
I have tried to repro this on a local server with two subscribing database
and can't get the expected behavior. IE an update on Subscriber1 makes it to
the publisher and Susbcriber2. Same with updates on Susbcriber2.
Also can you query the conflict tables directly on the Publisher and
subcriber just to verify the conflict viewer is correct.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408120524.77f6d0ca@.posting.google.c om...
> I do have some row filters on but none that comes into play in this
> particular situation.
|||This publication has 12 subscribers (push subscriptions) that resync
every 2 hours. They are staggered to run 5 minutes apart. This only
happens with one particular table and I think it is perhaps a bug that
is just not listing the conflict. So far I have determined that this
happens when a personnel record is update on two different
subscribers. The only thing is in this case the fields being updated
on the two different subscribers do not overlap. So there should not
be any conflicts, and none of them are showing up in the conflict
manager. I also queried the conflict table and there were no rows in
there.
For example:
Field a and b are changed on subscriber X
Field d and c are changed on subscriber Y
publisher resyncs with X and gets the changes to field a and b
publisher resyncs with Y and gets changes to field d and c but does
update
Y with the changes from X.
I hope this helps, I am currently trying to reproduce this to have get
a more detailed version of what is going down. Thank you very much
for your help Hilary, it is appreciated.

Replication performing UPDATES as INSERTS and DELETES on the subscriber

I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.

Why does it do this?

How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?

Thanks

Cut paste from a different thread, see if this helps:

>> If you can limit the update to unique columns as singleton (each update affects one row) , SQL2000 will replicate it as update. Otherwise for multi-row updates to unique columns you may consider replication of ‘proc exec’ instead.

>> You should also look at the parameter @.schema_option for sp_addarticle. There’s an option that marks FKs at the subscriber as Not For Replication, this may be what you’re looking for. Enabling this should work if you don’t have any other fancy business-triggers at the subscriber that are breaking due to the delete/insert.

|||

What's happening is called a deferred update. See kb 238254 for more info. This is a by design behavior.

Is this causing any problems in your scenario? Are subscriber-side triggers breaking? Are you getting referential integrity errors?

Two common workarounds are to replicate the update via proc exec, or enable schema option 0x20000 in sp_addarticle, which does not enforce RI constraints if raised by replication agent.