|
|
You're in the
sql server replication
group:Merge replication is deleting records
sql server replication:
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
I don't have any filters
Thanks [quoted text, click to view] "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. ] [quoted text, click to view] "Jennyfer J Barco" <pdwhitt@nospam.wdsinc.com> wrote in message news:urTZBYCdEHA.3728@TK2MSFTNGP09.phx.gbl... > 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 > > > > > >
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
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
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.aspx?scid=kb;en-us;828637&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. ] [quoted text, click to view] "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 > 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... > > > I don't have any fiters > > > 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 > > > > > > > > > > > > > > > > > > > >
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 [quoted text, click to view] "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... > > I don't have any fiters > > 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 > > > > > > > > > > > >
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 [quoted text, click to view] "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 > 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... > > > I don't have any fiters > > > 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 > > > > > > > > > > > > > > > > > > > >
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 [quoted text, click to view] "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.aspx?scid=kb;en-us;828637&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 > > 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... > > > > I don't have any fiters > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
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. ] [quoted text, click to view] "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 > 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.aspx?scid=kb;en-us;828637&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 > > > 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... > > > > > I don't have any fiters > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Yes, you can take that option OFF.
If you uncheck "Enforce relationship for replication" this means that you are making the PKs and FKs as NOT FOR REPLICATION as I had indicated below. You can try the above and let me know if it works. Hope that helps --Mahesh p.s. And it will help if you want to backup the master file just in case. Make a copy of it so that in the worst case, if something bad happens, you can populate it like it was before you did anything. [ This posting is provided "as is" with no warranties and confers no rights. ] [quoted text, click to view] "Jennyfer J Barco" <pdwhitt@nospam.wdsinc.com> wrote in message news:u0NqSdldEHA.3632@TK2MSFTNGP11.phx.gbl... > Thanks Mahesh, you have been so helpful. > > I have the option "Enforce relationship for replication" in the Publisher > and subscriber. Should I take it off? I was thinking to eliminate the FK in > the subscriber not to have this error but I would not like to do it. > > Now the replication is stopped until we take a desition, we have to make > sure that the records in our master file which are very important because > they have the stock in inventory won't be deleted. > > Thanks > jennyfer > > "Mahesh [MSFT]" <maheshrd@hotmail.com> wrote in message > news:eYJADPSdEHA.3260@TK2MSFTNGP09.phx.gbl... > > 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 > > > 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.aspx?scid=kb;en-us;828637&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 > > > > > 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... > > > > > > > I don't have any fiters > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Thanks Mahesh, you have been so helpful.
I have the option "Enforce relationship for replication" in the Publisher and subscriber. Should I take it off? I was thinking to eliminate the FK in the subscriber not to have this error but I would not like to do it. Now the replication is stopped until we take a desition, we have to make sure that the records in our master file which are very important because they have the stock in inventory won't be deleted. Thanks jennyfer [quoted text, click to view] "Mahesh [MSFT]" <maheshrd@hotmail.com> wrote in message news:eYJADPSdEHA.3260@TK2MSFTNGP09.phx.gbl... > 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 > > 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.aspx?scid=kb;en-us;828637&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 > > > > 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... > > > > > > I don't have any fiters > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Thanks so much, we'll do it next week after all the process of end of month
and I'll let you know. [quoted text, click to view] "Mahesh [MSFT]" <maheshrd@hotmail.com> wrote in message
news:eHcz%23wldEHA.644@tk2msftngp13.phx.gbl... > Yes, you can take that option OFF. > If you uncheck "Enforce relationship for replication" this means that you > are making the PKs and FKs as NOT FOR REPLICATION as I had indicated below. > You can try the above and let me know if it works. > > Hope that helps > --Mahesh > p.s. And it will help if you want to backup the master file just in case. > Make a copy of it so that in the worst case, if something bad happens, you > can populate it like it was before you did anything. > > [ This posting is provided "as is" with no warranties and confers no > rights. ] > > "Jennyfer J Barco" <pdwhitt@nospam.wdsinc.com> wrote in message > news:u0NqSdldEHA.3632@TK2MSFTNGP11.phx.gbl... > > Thanks Mahesh, you have been so helpful. > > > > I have the option "Enforce relationship for replication" in the Publisher > > and subscriber. Should I take it off? I was thinking to eliminate the FK > in > > the subscriber not to have this error but I would not like to do it. > > > > Now the replication is stopped until we take a desition, we have to make > > sure that the records in our master file which are very important because > > they have the stock in inventory won't be deleted. > > > > Thanks > > jennyfer > > > > "Mahesh [MSFT]" <maheshrd@hotmail.com> wrote in message > > news:eYJADPSdEHA.3260@TK2MSFTNGP09.phx.gbl... > > > 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 > > > > 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.aspx?scid=kb;en-us;828637&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 > > > > > > 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... > > > > > > > > I don't have any fiters > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |