| Groups | Blog | Home |
sql server replication : Synch failures cause deletes in FK table
can be caused by synching failures. For example, we have a table named People that is created and then a 2nd table record is created with a FK (ApplicantInfo) that links to the PK on the People table. For some reason, the 2nd table record gets created, but then mysteriously gets lost later that day or the next day. Can anything in synch be doing this and if so, where can I find it? I looked in the conflicts, but there is nothing there for that 2nd table for these new records. Thanks. David
I have new information on this. I tested the process here and got the error
below (found in conflict viewer) when synching the distributor: The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. Then I looked up the record on the main table of the Publisher and it was still there. Next, I went into conflict viewer and said "Keep the winning change" which was the Publisher. Next, I restarted the subscriber workstation and syncronized again. I got a message that a record was deleted!!!! I looked on both the Publisher AND Subscriber and the record was gone on both! Help. David [quoted text, click to view] "David" <dlchase@lifetimeinc.com> wrote in message news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... > We are experiencing lost records in some FK tables and I don't know if it > can be caused by synching failures. For example, we have a table named > People that is created and then a 2nd table record is created with a FK > (ApplicantInfo) that links to the PK on the People table. For some > reason, the 2nd table record gets created, but then mysteriously gets lost > later that day or the next day. Can anything in synch be doing this and > if so, where can I find it? I looked in the conflicts, but there is > nothing there for that 2nd table for these new records. Thanks. > > David >
Where is that setting? Thanks.
David [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... > This looks like a system delete. Change compensate_for_errors to false > > -- > 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 > > "David Chase" <dlchase@lifetimeinc.com> wrote in message > news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>I have new information on this. I tested the process here and got the >>error below (found in conflict viewer) when synching the distributor: >> >> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted >> at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on >> lock resources with another process and has been chosen as the deadlock >> victim. Rerun the transaction. >> >> >> >> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >> >> >> >> Then I looked up the record on the main table of the Publisher and it was >> still there. >> >> Next, I went into conflict viewer and said "Keep the winning change" >> which was the Publisher. >> >> Next, I restarted the subscriber workstation and syncronized again. I >> got a message that a record was deleted!!!! >> >> I looked on both the Publisher AND Subscriber and the record was gone on >> both! >> >> >> >> Help. >> >> >> >> David >> >> >> >> >> >> >> "David" <dlchase@lifetimeinc.com> wrote in message >> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>> We are experiencing lost records in some FK tables and I don't know if >>> it can be caused by synching failures. For example, we have a table >>> named People that is created and then a 2nd table record is created with >>> a FK (ApplicantInfo) that links to the PK on the People table. For some >>> reason, the 2nd table record gets created, but then mysteriously gets >>> lost later that day or the next day. Can anything in synch be doing >>> this and if so, where can I find it? I looked in the conflicts, but >>> there is nothing there for that 2nd table for these new records. >>> Thanks. >>> >>> David >>> >> >> > >
How do these things happen when you merely accept all of the defaults when
setting up replication? David [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... > This looks like a system delete. Change compensate_for_errors to false > > -- > 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 > > "David Chase" <dlchase@lifetimeinc.com> wrote in message > news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>I have new information on this. I tested the process here and got the >>error below (found in conflict viewer) when synching the distributor: >> >> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted >> at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on >> lock resources with another process and has been chosen as the deadlock >> victim. Rerun the transaction. >> >> >> >> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >> >> >> >> Then I looked up the record on the main table of the Publisher and it was >> still there. >> >> Next, I went into conflict viewer and said "Keep the winning change" >> which was the Publisher. >> >> Next, I restarted the subscriber workstation and syncronized again. I >> got a message that a record was deleted!!!! >> >> I looked on both the Publisher AND Subscriber and the record was gone on >> both! >> >> >> >> Help. >> >> >> >> David >> >> >> >> >> >> >> "David" <dlchase@lifetimeinc.com> wrote in message >> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>> We are experiencing lost records in some FK tables and I don't know if >>> it can be caused by synching failures. For example, we have a table >>> named People that is created and then a 2nd table record is created with >>> a FK (ApplicantInfo) that links to the PK on the People table. For some >>> reason, the 2nd table record gets created, but then mysteriously gets >>> lost later that day or the next day. Can anything in synch be doing >>> this and if so, where can I find it? I looked in the conflicts, but >>> there is nothing there for that 2nd table for these new records. >>> Thanks. >>> >>> David >>> >> >> > >
This looks like a system delete. Change compensate_for_errors to false
-- 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 [quoted text, click to view] "David Chase" <dlchase@lifetimeinc.com> wrote in message news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >I have new information on this. I tested the process here and got the >error below (found in conflict viewer) when synching the distributor: > > The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted > at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on > lock resources with another process and has been chosen as the deadlock > victim. Rerun the transaction. > > > > p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. > > > > Then I looked up the record on the main table of the Publisher and it was > still there. > > Next, I went into conflict viewer and said "Keep the winning change" which > was the Publisher. > > Next, I restarted the subscriber workstation and syncronized again. I got > a message that a record was deleted!!!! > > I looked on both the Publisher AND Subscriber and the record was gone on > both! > > > > Help. > > > > David > > > > > > > "David" <dlchase@lifetimeinc.com> wrote in message > news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >> We are experiencing lost records in some FK tables and I don't know if it >> can be caused by synching failures. For example, we have a table named >> People that is created and then a 2nd table record is created with a FK >> (ApplicantInfo) that links to the PK on the People table. For some >> reason, the 2nd table record gets created, but then mysteriously gets >> lost later that day or the next day. Can anything in synch be doing this >> and if so, where can I find it? I looked in the conflicts, but there is >> nothing there for that 2nd table for these new records. Thanks. >> >> David >> > >
the default in SQL 2000 is true, in SQL 2005 its false. Microsoft's defaults
are very carefully field tested to ensure that they match the majority of their customers needs. -- 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 [quoted text, click to view] "David" <dlchase@lifetimeinc.com> wrote in message news:eHHd1S5IGHA.1180@TK2MSFTNGP09.phx.gbl... > How do these things happen when you merely accept all of the defaults when > setting up replication? > > David > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... >> This looks like a system delete. Change compensate_for_errors to false >> >> -- >> 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 >> >> "David Chase" <dlchase@lifetimeinc.com> wrote in message >> news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>>I have new information on this. I tested the process here and got the >>>error below (found in conflict viewer) when synching the distributor: >>> >>> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted >>> at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on >>> lock resources with another process and has been chosen as the deadlock >>> victim. Rerun the transaction. >>> >>> >>> >>> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >>> >>> >>> >>> Then I looked up the record on the main table of the Publisher and it >>> was still there. >>> >>> Next, I went into conflict viewer and said "Keep the winning change" >>> which was the Publisher. >>> >>> Next, I restarted the subscriber workstation and syncronized again. I >>> got a message that a record was deleted!!!! >>> >>> I looked on both the Publisher AND Subscriber and the record was gone on >>> both! >>> >>> >>> >>> Help. >>> >>> >>> >>> David >>> >>> >>> >>> >>> >>> >>> "David" <dlchase@lifetimeinc.com> wrote in message >>> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>>> We are experiencing lost records in some FK tables and I don't know if >>>> it can be caused by synching failures. For example, we have a table >>>> named People that is created and then a 2nd table record is created >>>> with a FK (ApplicantInfo) that links to the PK on the People table. >>>> For some reason, the 2nd table record gets created, but then >>>> mysteriously gets lost later that day or the next day. Can anything in >>>> synch be doing this and if so, where can I find it? I looked in the >>>> conflicts, but there is nothing there for that 2nd table for these new >>>> records. Thanks. >>>> >>>> David >>>> >>> >>> >> >> > >
sp_addmergearticle
-- 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 [quoted text, click to view] "David" <dlchase@lifetimeinc.com> wrote in message news:%23fvv7O5IGHA.2212@TK2MSFTNGP15.phx.gbl... > Where is that setting? Thanks. > > David > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... >> This looks like a system delete. Change compensate_for_errors to false >> >> -- >> 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 >> >> "David Chase" <dlchase@lifetimeinc.com> wrote in message >> news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>>I have new information on this. I tested the process here and got the >>>error below (found in conflict viewer) when synching the distributor: >>> >>> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted >>> at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was deadlocked on >>> lock resources with another process and has been chosen as the deadlock >>> victim. Rerun the transaction. >>> >>> >>> >>> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >>> >>> >>> >>> Then I looked up the record on the main table of the Publisher and it >>> was still there. >>> >>> Next, I went into conflict viewer and said "Keep the winning change" >>> which was the Publisher. >>> >>> Next, I restarted the subscriber workstation and syncronized again. I >>> got a message that a record was deleted!!!! >>> >>> I looked on both the Publisher AND Subscriber and the record was gone on >>> both! >>> >>> >>> >>> Help. >>> >>> >>> >>> David >>> >>> >>> >>> >>> >>> >>> "David" <dlchase@lifetimeinc.com> wrote in message >>> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>>> We are experiencing lost records in some FK tables and I don't know if >>>> it can be caused by synching failures. For example, we have a table >>>> named People that is created and then a 2nd table record is created >>>> with a FK (ApplicantInfo) that links to the PK on the People table. >>>> For some reason, the 2nd table record gets created, but then >>>> mysteriously gets lost later that day or the next day. Can anything in >>>> synch be doing this and if so, where can I find it? I looked in the >>>> conflicts, but there is nothing there for that 2nd table for these new >>>> records. Thanks. >>>> >>>> David >>>> >>> >>> >> >> > >
Thanks. Is there a setting also when I initially create/publish the
articles? David [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uEeVumAJGHA.3896@TK2MSFTNGP15.phx.gbl... > sp_addmergearticle > > -- > 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 > > "David" <dlchase@lifetimeinc.com> wrote in message > news:%23fvv7O5IGHA.2212@TK2MSFTNGP15.phx.gbl... >> Where is that setting? Thanks. >> >> David >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... >>> This looks like a system delete. Change compensate_for_errors to false >>> >>> -- >>> 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 >>> >>> "David Chase" <dlchase@lifetimeinc.com> wrote in message >>> news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>>>I have new information on this. I tested the process here and got the >>>>error below (found in conflict viewer) when synching the distributor: >>>> >>>> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be >>>> inserted at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was >>>> deadlocked on lock resources with another process and has been chosen >>>> as the deadlock victim. Rerun the transaction. >>>> >>>> >>>> >>>> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >>>> >>>> >>>> >>>> Then I looked up the record on the main table of the Publisher and it >>>> was still there. >>>> >>>> Next, I went into conflict viewer and said "Keep the winning change" >>>> which was the Publisher. >>>> >>>> Next, I restarted the subscriber workstation and syncronized again. I >>>> got a message that a record was deleted!!!! >>>> >>>> I looked on both the Publisher AND Subscriber and the record was gone >>>> on both! >>>> >>>> >>>> >>>> Help. >>>> >>>> >>>> >>>> David >>>> >>>> >>>> >>>> >>>> >>>> >>>> "David" <dlchase@lifetimeinc.com> wrote in message >>>> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>>>> We are experiencing lost records in some FK tables and I don't know if >>>>> it can be caused by synching failures. For example, we have a table >>>>> named People that is created and then a 2nd table record is created >>>>> with a FK (ApplicantInfo) that links to the PK on the People table. >>>>> For some reason, the 2nd table record gets created, but then >>>>> mysteriously gets lost later that day or the next day. Can anything >>>>> in synch be doing this and if so, where can I find it? I looked in >>>>> the conflicts, but there is nothing there for that 2nd table for these >>>>> new records. Thanks. >>>>> >>>>> David >>>>> >>>> >>>> >>> >>> >> >> > >
No, that's the only one.
-- 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 [quoted text, click to view] "David Chase" <dlchase@lifetimeinc.com> wrote in message news:O8zfNoCJGHA.1760@TK2MSFTNGP10.phx.gbl... > Thanks. Is there a setting also when I initially create/publish the > articles? > David > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:uEeVumAJGHA.3896@TK2MSFTNGP15.phx.gbl... >> sp_addmergearticle >> >> -- >> 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 >> >> "David" <dlchase@lifetimeinc.com> wrote in message >> news:%23fvv7O5IGHA.2212@TK2MSFTNGP15.phx.gbl... >>> Where is that setting? Thanks. >>> >>> David >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:%23$uqo44IGHA.3936@TK2MSFTNGP10.phx.gbl... >>>> This looks like a system delete. Change compensate_for_errors to false >>>> >>>> -- >>>> 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 >>>> >>>> "David Chase" <dlchase@lifetimeinc.com> wrote in message >>>> news:%23R4F7%233IGHA.2704@TK2MSFTNGP15.phx.gbl... >>>>>I have new information on this. I tested the process here and got the >>>>>error below (found in conflict viewer) when synching the distributor: >>>>> >>>>> The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be >>>>> inserted at 'DELLGX260.MCFIData'. Transaction (Process ID 53) was >>>>> deadlocked on lock resources with another process and has been chosen >>>>> as the deadlock victim. Rerun the transaction. >>>>> >>>>> >>>>> >>>>> p.s. LIFEDEVTEST is the Publisher and DELLGX620 is the Subscriber. >>>>> >>>>> >>>>> >>>>> Then I looked up the record on the main table of the Publisher and it >>>>> was still there. >>>>> >>>>> Next, I went into conflict viewer and said "Keep the winning change" >>>>> which was the Publisher. >>>>> >>>>> Next, I restarted the subscriber workstation and syncronized again. I >>>>> got a message that a record was deleted!!!! >>>>> >>>>> I looked on both the Publisher AND Subscriber and the record was gone >>>>> on both! >>>>> >>>>> >>>>> >>>>> Help. >>>>> >>>>> >>>>> >>>>> David >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> "David" <dlchase@lifetimeinc.com> wrote in message >>>>> news:eUssIU3IGHA.1124@TK2MSFTNGP10.phx.gbl... >>>>>> We are experiencing lost records in some FK tables and I don't know >>>>>> if it can be caused by synching failures. For example, we have a >>>>>> table named People that is created and then a 2nd table record is >>>>>> created with a FK (ApplicantInfo) that links to the PK on the People >>>>>> table. For some reason, the 2nd table record gets created, but then >>>>>> mysteriously gets lost later that day or the next day. Can anything >>>>>> in synch be doing this and if so, where can I find it? I looked in >>>>>> the conflicts, but there is nothing there for that 2nd table for >>>>>> these new records. Thanks. >>>>>> >>>>>> David >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
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
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |