compensating delete on replication database!

Hi
I have Merge Publication with just one Subscriber running on SQL 2000
which has been Service packed fully.
Looking at the previous posts I did uncheck 'Enforce relationship for
replication' on the Table Relationships hoping that the deletion of
records will not happen.
But for some unknown reason the records in the FK relationship is
deleted, which is causing all sorts of problems. There are no Triggers
on the database, and I could not find a pattern for the deletion it
happens randomly.
E.G. We have a 'Contacts' and 'Addresses' Table, one contact can have
more than 1 address. But for some reason the addresses are being deleted
for the contact, i am not sure where it is happening whether in
Subscriber or Publisher, but we loose the record.
Does any one has a good solution for this?
Thanks in advance.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Do you have any user triggers at all?
Perhaps you are experiencing the compensating commands situation :
http://support.microsoft.com/default...Product=sql2k?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Thanks Paul for the link. But I do not have any user triggers at all?
Since the 'Enforce relationship for replication' is unchecked, I am a
bit puzzled about how this is happening.
Can I apply the hotfix (the link you have sent) to the production
server? Is it safe?
Thanks once again.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
The Hotfix is available only in SP4 Beta and not sure when it is going
to be released. In the meantime the database is loosing the records all
the way through. Can you suggest any intermediate solutions? somethings
I need to check etc..
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The two suggestions weren't related - user triggers was just one other
posibility.
If it is a compensation issue, you should be able to reproduce. An address
added successfully at one site will be deleted if it cannot be applied at
the other due to a PK/FK error, constraint error etc. So, you need to check
that the schemas are identical wrt these tables (SQLCompare?). If you can
find an address that repeatedly gets removed then tracking/removing the
constraint should be simple.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Hi Paul
Thanks for the reply. There is not pattern these records are deleted and
I am not able to re-create it as well.
The Addresses Table has 2 FK relationship one to the AddressType Table
and one to the Contact Table were the records are all present. It is
only the Address records that gets deleted.
Any other areas to look into, please...
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
I am really sorry, there is a Trigger created by another developer on
the Addresses Table, which maintaines the Ref. Intergerity. Could this
be the cause for this 'compensating deletion', if so how to overcome
this, do we need to remove the triggers?
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Compensating Changes - Deletes Good Rows From a Table?

We have some tables set up doing merge replication, and just came across
http://support.microsoft.com/default...&Product=sql2k
Is this gist of this, that when a compensating change condition exists, that
potentially good rows that cannot be replicated out, are instead DELETED
from the source table?
Is it logged somewhere when this has happened, or is there a way to monitor
it otherwise?
Thanks!
They are logged as conflicts but remain in the publisher/subscriber.
So if you insert a row with the same pk value in the publisher and
subscriber, when the sync happens the rows remain in the publisher and
subscriber.
If you update a row on the subscriber and delete it on the publisher, the
row remains updated on the subscriber and is not deleted there and remains
deleted on the publisher.
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
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:OR2q7S6%23FHA.264@.tk2msftngp13.phx.gbl...
> We have some tables set up doing merge replication, and just came across
> http://support.microsoft.com/default...&Product=sql2k
> Is this gist of this, that when a compensating change condition exists,
> that potentially good rows that cannot be replicated out, are instead
> DELETED from the source table?
> Is it logged somewhere when this has happened, or is there a way to
> monitor it otherwise?
> Thanks!
>
>
>