Merge replication is deleting records

I have a merge replication of some tables between two servers. Something
curious happens. When I insert a register in a table in one database, then I
wait and the other table in the database doesn't update the new register and
I refresh the view in my original database and the record I inserted is not
there anymore. Is like when I added the record he found that is not in the
other site so he thinks he should delete it instead of inserting in the
other site. Why this happens? Is it a bug of replication?
This is scaring me because the same problem in this lilte table is happening
in a master table where I keep inventory, by some reason sometimes (not very
often) a record is being deleted and I sopposed is for the replication
because the process inserts the record in one place and then he finds that
this record is not in the other place and instead of inserting he deletes
the record.
Please help me because I can't miss any of this records, what can I do, how
can I trace what happened? I created a trigger for delete that inserts in a
new table every time a register is being deleted in this master table and he
inserted the deleted record in this table but how can I trace how was
deleted?
Please help me with this replication problem
Thanks in advance
Jennyfer
Jennyfer ,
do you have filters on the publication?
This scenario could occur if the record doesn't satisfy the filter, so an
insert on the subscriber will be uploaded then downloaded as a delete.
HTH,
Paul Ibison
|||I don't have any filters
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23e$%231fBdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Jennyfer ,
> do you have filters on the publication?
> This scenario could occur if the record doesn't satisfy the filter, so an
> insert on the subscriber will be uploaded then downloaded as a delete.
> HTH,
> Paul Ibison
>
|||Would it be the case that this insert violates some constraint (PK, FK,
check, etc) at the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:urTZBYCdEHA.3728@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't have any filters
> Thanks
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23e$%231fBdEHA.4092@.TK2MSFTNGP10.phx.gbl...
an
>
|||To find out a bit more info you might want to...
To see who is doing the delete...select publisher_insertcount,
publisher_updatecount, publisher_deletecount,
subscriber_insertcount, subscriber_updatecount, subscriber_deletecount
from dbo.MSmerge_history
Run profiler on the publisher and subscriber to see why/how the delete is
being done.
Check the conflict viewer to see if any conflicts are registered.
HTH,
Paul Ibison
|||Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
the master table that the product register should exists in another table
wich is beign replicated too. It looks that he tryed to insert the master
register and the other table was not replicated yet so the error in the
confict_Mergename_mastertable table says that the insert in the other site
conflicted with the FOREIGN KEY and my records that were deleted are in this
table. The thing is, if there is a problem like this and SQL can't insert
the register in the other site but should not delete the record in my site
where it was originated inserted. Is there any settings where I can say that
if an insert error comes don't delete the original record just write in this
log table that the record was not replicated. Please give me an idea and
thanks so much for you help and tips
Jennyfer
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Would it be the case that this insert violates some constraint (PK, FK,
> check, etc) at the subscriber?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:urTZBYCdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> an
>
|||One thing that caused this was because the replication stopped wth no reason
in the weekend and then we started again. It looks thar SQL trys to insert
all the transactions that were not inserted while it was stopped and in this
there is no order or priority with the tables( maybe he sorts
alphabeticaly). May I define a priority so always when I start synchronizing
the outer reference tables comes first? May I select the order of the tables
in which I want the replication to start?
Thanks so much
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
> the master table that the product register should exists in another table
> wich is beign replicated too. It looks that he tryed to insert the master
> register and the other table was not replicated yet so the error in the
> confict_Mergename_mastertable table says that the insert in the other site
> conflicted with the FOREIGN KEY and my records that were deleted are in
this
> table. The thing is, if there is a problem like this and SQL can't insert
> the register in the other site but should not delete the record in my site
> where it was originated inserted. Is there any settings where I can say
that
> if an insert error comes don't delete the original record just write in
this[vbcol=seagreen]
> log table that the record was not replicated. Please give me an idea and
> thanks so much for you help and tips
> Jennyfer
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
so[vbcol=seagreen]
delete.
>
|||Basically you are running into the compensate_for_errors problem.
This problem is that if a change from publisher (say) fails to get applied
at the subscriber (for some reason, PK,FK,CHECK,etc constraints) it undoes
the change at the publisher. So a insert from publisher when fails at the
subscriber gets deleted at the publisher too. Similary a delete from
publisher when fails at the subscriber, it gets re-inserted at the
publisher.
The KB article for that is here:
http://support.microsoft.com/default...&Product=sql2k
This is fixed in a QFE and please contact PSS for the QFE.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
> the master table that the product register should exists in another table
> wich is beign replicated too. It looks that he tryed to insert the master
> register and the other table was not replicated yet so the error in the
> confict_Mergename_mastertable table says that the insert in the other site
> conflicted with the FOREIGN KEY and my records that were deleted are in
this
> table. The thing is, if there is a problem like this and SQL can't insert
> the register in the other site but should not delete the record in my site
> where it was originated inserted. Is there any settings where I can say
that
> if an insert error comes don't delete the original record just write in
this[vbcol=seagreen]
> log table that the record was not replicated. Please give me an idea and
> thanks so much for you help and tips
> Jennyfer
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
so[vbcol=seagreen]
delete.
>
|||Don't forget this:
One thing that caused this was because the replication stopped wth no reason
in the weekend and then we started again. It looks thar SQL trys to insert
all the transactions that were not inserted while it was stopped and in this
there is no order or priority with the tables( maybe he sorts
alphabeticaly). May I define a priority so always when I start synchronizing
the outer reference tables comes first? May I select the order of the tables
in which I want the replication to start?
Thanks so much
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:%23Yz$O9MdEHA.1692@.tk2msftngp13.phx.gbl...
> Basically you are running into the compensate_for_errors problem.
> This problem is that if a change from publisher (say) fails to get applied
> at the subscriber (for some reason, PK,FK,CHECK,etc constraints) it undoes
> the change at the publisher. So a insert from publisher when fails at the
> subscriber gets deleted at the publisher too. Similary a delete from
> publisher when fails at the subscriber, it gets re-inserted at the
> publisher.
> The KB article for that is here:
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> This is fixed in a QFE and please contact PSS for the QFE.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
master[vbcol=seagreen]
site[vbcol=seagreen]
> this
insert[vbcol=seagreen]
site[vbcol=seagreen]
> that
> this
FK,[vbcol=seagreen]
filter,
> so
> delete.
>
|||You cannot specify the processing order in SQL Server 2000.
There is a possibility as you say that some changes could go before the
others that can cause this kind of conflicting behavior.
However if the PKs and FKs were defined as NOT FOR REPLICATION then probably
you would not have hit this error.
Or had there been merge filters between the articles, probably they would
have been enumerated in the proper order too.
So what is the current state? Has it synched up now?
If you make any inserts to tables at the publisher do you see them
propagated to the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uxUYZaOdEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Don't forget this:
> One thing that caused this was because the replication stopped wth no
reason
> in the weekend and then we started again. It looks thar SQL trys to insert
> all the transactions that were not inserted while it was stopped and in
this
> there is no order or priority with the tables( maybe he sorts
> alphabeticaly). May I define a priority so always when I start
synchronizing
> the outer reference tables comes first? May I select the order of the
tables[vbcol=seagreen]
> in which I want the replication to start?
> Thanks so much
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:%23Yz$O9MdEHA.1692@.tk2msftngp13.phx.gbl...
applied[vbcol=seagreen]
undoes[vbcol=seagreen]
the
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
KEY[vbcol=seagreen]
> in
> table
> master
the[vbcol=seagreen]
> site
in[vbcol=seagreen]
> insert
> site
say[vbcol=seagreen]
in[vbcol=seagreen]
and
> FK,
> filter,
>