|
|
You're in the
sql server replication
group:UPDATE Statements May be Replicated as DELETE/INSERT Pairs
sql server replication:
logged in the tlog as an delete insert pair. The log reader will construct an equivalent update statement from this delete insert pair - unless an update in place cannot occur (ie you are updating a pk amoung other events). I spoke with someone on the query optimizer team about this and he said it was for efficiency in the command construction process (IIRC). I didn't really follow much more of what he said. Is it also possible that you have cascading updates and deletes and you have not checked the enforce constraint for replicatioin option? -- 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] "Asser Maany" <asser.maany@microtech.com.eg> wrote in message news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... > Hi There, > > I was troubleshooting a problem in my transactional replication and > notices that SQL Server replicates some of my update statements as > Delete/Insert pairs, I was really surprised and couldn't understand why > SQL server does that, I found an article in MS support web site > http://support.microsoft.com/kb/238254/EN-US/ that is saying more or less > that this might happen in some cases, still not convinced why, but this is > a fact of life. > > > > Found also another article saying that there is a trace flag that would > force SQL server to perform an update as an update (not Delete/Insert > pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in all > cases, again failed to understand why. > > > > I'm struggling now to understand why does SQL server do that and also want > a solution from my problem since these delete/insert statements are > conflicting with my database referential integrity, imagine that I'm > updating an employee record that is used in almost all my tables and has > got foreign keys every where referencing it and SQL Server is deleting > this employee when I perform an update on the publisher!! > > > > In the first article they are saying that I should change the logic of my > triggers or replication stored procedures to handle this case, but how can > I detect that the first delete is followed by an insert for the same > record and convert them as an update. > > > > If you faced this situation before or you have a solution for this dilemma > ,please share your experience with me. > > > > Thanks in advance > > Asser Maany > >
how about you make the fk's and all constraints Not For Replication?
-- 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] "Asser Maany" <asser.maany@microtech.com.eg> wrote in message news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... > Thanks for the fast reply, but my point now is how should I deal with it > to enforce the referential integrity, as I said in my employee example, > the delete that SQL server sends will cause a foreign key violation? > > > Regards > Asser Maany > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >> If a table is published for replication and updates occurring on it are >> logged in the tlog as an delete insert pair. The log reader will >> construct an equivalent update statement from this delete insert pair - >> unless an update in place cannot occur (ie you are updating a pk amoung >> other events). >> >> I spoke with someone on the query optimizer team about this and he said >> it was for efficiency in the command construction process (IIRC). I >> didn't really follow much more of what he said. Is it also possible that >> you have cascading updates and deletes and you have not checked the >> enforce constraint for replicatioin option? >> >> -- >> 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 >> >> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>> Hi There, >>> >>> I was troubleshooting a problem in my transactional replication and >>> notices that SQL Server replicates some of my update statements as >>> Delete/Insert pairs, I was really surprised and couldn't understand why >>> SQL server does that, I found an article in MS support web site >>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>> less that this might happen in some cases, still not convinced why, but >>> this is a fact of life. >>> >>> >>> >>> Found also another article saying that there is a trace flag that would >>> force SQL server to perform an update as an update (not Delete/Insert >>> pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in >>> all cases, again failed to understand why. >>> >>> >>> >>> I'm struggling now to understand why does SQL server do that and also >>> want a solution from my problem since these delete/insert statements are >>> conflicting with my database referential integrity, imagine that I'm >>> updating an employee record that is used in almost all my tables and has >>> got foreign keys every where referencing it and SQL Server is deleting >>> this employee when I perform an update on the publisher!! >>> >>> >>> >>> In the first article they are saying that I should change the logic of >>> my triggers or replication stored procedures to handle this case, but >>> how can I detect that the first delete is followed by an insert for the >>> same record and convert them as an update. >>> >>> >>> >>> If you faced this situation before or you have a solution for this >>> dilemma ,please share your experience with me. >>> >>> >>> >>> Thanks in advance >>> >>> Asser Maany >>> >>> >> >> > >
Yes, if you know the next action will fix it, or fill in the missing DRI
pieces. -- 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] "Asser Maany" <asser.maany@microtech.com.eg> wrote in message news:%23JPmaKBMGHA.532@TK2MSFTNGP15.phx.gbl... > Possible, but would this be a good practice. > > > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23ZV6$HBMGHA.1424@TK2MSFTNGP12.phx.gbl... >> how about you make the fk's and all constraints Not For Replication? >> >> -- >> 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 >> >> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >> news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... >>> Thanks for the fast reply, but my point now is how should I deal with it >>> to enforce the referential integrity, as I said in my employee example, >>> the delete that SQL server sends will cause a foreign key violation? >>> >>> >>> Regards >>> Asser Maany >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >>>> If a table is published for replication and updates occurring on it are >>>> logged in the tlog as an delete insert pair. The log reader will >>>> construct an equivalent update statement from this delete insert pair - >>>> unless an update in place cannot occur (ie you are updating a pk amoung >>>> other events). >>>> >>>> I spoke with someone on the query optimizer team about this and he said >>>> it was for efficiency in the command construction process (IIRC). I >>>> didn't really follow much more of what he said. Is it also possible >>>> that you have cascading updates and deletes and you have not checked >>>> the enforce constraint for replicatioin option? >>>> >>>> -- >>>> 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 >>>> >>>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>>> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>>>> Hi There, >>>>> >>>>> I was troubleshooting a problem in my transactional replication and >>>>> notices that SQL Server replicates some of my update statements as >>>>> Delete/Insert pairs, I was really surprised and couldn't understand >>>>> why SQL server does that, I found an article in MS support web site >>>>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>>>> less that this might happen in some cases, still not convinced why, >>>>> but this is a fact of life. >>>>> >>>>> >>>>> >>>>> Found also another article saying that there is a trace flag that >>>>> would force SQL server to perform an update as an update (not >>>>> Delete/Insert pair) (http://support.microsoft.com/kb/302341/EN-US/) >>>>> but still not in all cases, again failed to understand why. >>>>> >>>>> >>>>> >>>>> I'm struggling now to understand why does SQL server do that and also >>>>> want a solution from my problem since these delete/insert statements >>>>> are conflicting with my database referential integrity, imagine that >>>>> I'm updating an employee record that is used in almost all my tables >>>>> and has got foreign keys every where referencing it and SQL Server is >>>>> deleting this employee when I perform an update on the publisher!! >>>>> >>>>> >>>>> >>>>> In the first article they are saying that I should change the logic of >>>>> my triggers or replication stored procedures to handle this case, but >>>>> how can I detect that the first delete is followed by an insert for >>>>> the same record and convert them as an update. >>>>> >>>>> >>>>> >>>>> If you faced this situation before or you have a solution for this >>>>> dilemma ,please share your experience with me. >>>>> >>>>> >>>>> >>>>> Thanks in advance >>>>> >>>>> Asser Maany >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Can you post the schema of the problem table(s) and also the problem update
delete statements? -- 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] "Asser Maany" <asser.maany@microtech.com.eg> wrote in message news:O0XjZJBMGHA.3408@TK2MSFTNGP12.phx.gbl... > Sorry, missed your last comments, there are no cascaded deletes or > updates, and I'm not updating the primary key, that is why I don't really > understand why is it converted as a delete and update. > > > > "Asser Maany" <asser.maany@microtech.com.eg> wrote in message > news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... >> Thanks for the fast reply, but my point now is how should I deal with it >> to enforce the referential integrity, as I said in my employee example, >> the delete that SQL server sends will cause a foreign key violation? >> >> >> Regards >> Asser Maany >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >>> If a table is published for replication and updates occurring on it are >>> logged in the tlog as an delete insert pair. The log reader will >>> construct an equivalent update statement from this delete insert pair - >>> unless an update in place cannot occur (ie you are updating a pk amoung >>> other events). >>> >>> I spoke with someone on the query optimizer team about this and he said >>> it was for efficiency in the command construction process (IIRC). I >>> didn't really follow much more of what he said. Is it also possible that >>> you have cascading updates and deletes and you have not checked the >>> enforce constraint for replicatioin option? >>> >>> -- >>> 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 >>> >>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>>> Hi There, >>>> >>>> I was troubleshooting a problem in my transactional replication and >>>> notices that SQL Server replicates some of my update statements as >>>> Delete/Insert pairs, I was really surprised and couldn't understand why >>>> SQL server does that, I found an article in MS support web site >>>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>>> less that this might happen in some cases, still not convinced why, but >>>> this is a fact of life. >>>> >>>> >>>> >>>> Found also another article saying that there is a trace flag that would >>>> force SQL server to perform an update as an update (not Delete/Insert >>>> pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in >>>> all cases, again failed to understand why. >>>> >>>> >>>> >>>> I'm struggling now to understand why does SQL server do that and also >>>> want a solution from my problem since these delete/insert statements >>>> are conflicting with my database referential integrity, imagine that >>>> I'm updating an employee record that is used in almost all my tables >>>> and has got foreign keys every where referencing it and SQL Server is >>>> deleting this employee when I perform an update on the publisher!! >>>> >>>> >>>> >>>> In the first article they are saying that I should change the logic of >>>> my triggers or replication stored procedures to handle this case, but >>>> how can I detect that the first delete is followed by an insert for the >>>> same record and convert them as an update. >>>> >>>> >>>> >>>> If you faced this situation before or you have a solution for this >>>> dilemma ,please share your experience with me. >>>> >>>> >>>> >>>> Thanks in advance >>>> >>>> Asser Maany >>>> >>>> >>> >>> >> >> > >
Hi There,
I was troubleshooting a problem in my transactional replication and notices that SQL Server replicates some of my update statements as Delete/Insert pairs, I was really surprised and couldn't understand why SQL server does that, I found an article in MS support web site http://support.microsoft.com/kb/238254/EN-US/ that is saying more or less that this might happen in some cases, still not convinced why, but this is a fact of life. Found also another article saying that there is a trace flag that would force SQL server to perform an update as an update (not Delete/Insert pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in all cases, again failed to understand why. I'm struggling now to understand why does SQL server do that and also want a solution from my problem since these delete/insert statements are conflicting with my database referential integrity, imagine that I'm updating an employee record that is used in almost all my tables and has got foreign keys every where referencing it and SQL Server is deleting this employee when I perform an update on the publisher!! In the first article they are saying that I should change the logic of my triggers or replication stored procedures to handle this case, but how can I detect that the first delete is followed by an insert for the same record and convert them as an update. If you faced this situation before or you have a solution for this dilemma ,please share your experience with me. Thanks in advance Asser Maany
Thanks for the fast reply, but my point now is how should I deal with it to
enforce the referential integrity, as I said in my employee example, the delete that SQL server sends will cause a foreign key violation? Regards Asser Maany [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... > If a table is published for replication and updates occurring on it are > logged in the tlog as an delete insert pair. The log reader will construct > an equivalent update statement from this delete insert pair - unless an > update in place cannot occur (ie you are updating a pk amoung other > events). > > I spoke with someone on the query optimizer team about this and he said it > was for efficiency in the command construction process (IIRC). I didn't > really follow much more of what he said. Is it also possible that you have > cascading updates and deletes and you have not checked the enforce > constraint for replicatioin option? > > -- > 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 > > "Asser Maany" <asser.maany@microtech.com.eg> wrote in message > news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >> Hi There, >> >> I was troubleshooting a problem in my transactional replication and >> notices that SQL Server replicates some of my update statements as >> Delete/Insert pairs, I was really surprised and couldn't understand why >> SQL server does that, I found an article in MS support web site >> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or less >> that this might happen in some cases, still not convinced why, but this >> is a fact of life. >> >> >> >> Found also another article saying that there is a trace flag that would >> force SQL server to perform an update as an update (not Delete/Insert >> pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in >> all cases, again failed to understand why. >> >> >> >> I'm struggling now to understand why does SQL server do that and also >> want a solution from my problem since these delete/insert statements are >> conflicting with my database referential integrity, imagine that I'm >> updating an employee record that is used in almost all my tables and has >> got foreign keys every where referencing it and SQL Server is deleting >> this employee when I perform an update on the publisher!! >> >> >> >> In the first article they are saying that I should change the logic of my >> triggers or replication stored procedures to handle this case, but how >> can I detect that the first delete is followed by an insert for the same >> record and convert them as an update. >> >> >> >> If you faced this situation before or you have a solution for this >> dilemma ,please share your experience with me. >> >> >> >> Thanks in advance >> >> Asser Maany >> >> > >
Sorry, missed your last comments, there are no cascaded deletes or updates,
and I'm not updating the primary key, that is why I don't really understand why is it converted as a delete and update. [quoted text, click to view] "Asser Maany" <asser.maany@microtech.com.eg> wrote in message news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... > Thanks for the fast reply, but my point now is how should I deal with it > to enforce the referential integrity, as I said in my employee example, > the delete that SQL server sends will cause a foreign key violation? > > > Regards > Asser Maany > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >> If a table is published for replication and updates occurring on it are >> logged in the tlog as an delete insert pair. The log reader will >> construct an equivalent update statement from this delete insert pair - >> unless an update in place cannot occur (ie you are updating a pk amoung >> other events). >> >> I spoke with someone on the query optimizer team about this and he said >> it was for efficiency in the command construction process (IIRC). I >> didn't really follow much more of what he said. Is it also possible that >> you have cascading updates and deletes and you have not checked the >> enforce constraint for replicatioin option? >> >> -- >> 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 >> >> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>> Hi There, >>> >>> I was troubleshooting a problem in my transactional replication and >>> notices that SQL Server replicates some of my update statements as >>> Delete/Insert pairs, I was really surprised and couldn't understand why >>> SQL server does that, I found an article in MS support web site >>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>> less that this might happen in some cases, still not convinced why, but >>> this is a fact of life. >>> >>> >>> >>> Found also another article saying that there is a trace flag that would >>> force SQL server to perform an update as an update (not Delete/Insert >>> pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in >>> all cases, again failed to understand why. >>> >>> >>> >>> I'm struggling now to understand why does SQL server do that and also >>> want a solution from my problem since these delete/insert statements are >>> conflicting with my database referential integrity, imagine that I'm >>> updating an employee record that is used in almost all my tables and has >>> got foreign keys every where referencing it and SQL Server is deleting >>> this employee when I perform an update on the publisher!! >>> >>> >>> >>> In the first article they are saying that I should change the logic of >>> my triggers or replication stored procedures to handle this case, but >>> how can I detect that the first delete is followed by an insert for the >>> same record and convert them as an update. >>> >>> >>> >>> If you faced this situation before or you have a solution for this >>> dilemma ,please share your experience with me. >>> >>> >>> >>> Thanks in advance >>> >>> Asser Maany >>> >>> >> >> > >
Possible, but would this be a good practice.
[quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23ZV6$HBMGHA.1424@TK2MSFTNGP12.phx.gbl... > how about you make the fk's and all constraints Not For Replication? > > -- > 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 > > "Asser Maany" <asser.maany@microtech.com.eg> wrote in message > news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... >> Thanks for the fast reply, but my point now is how should I deal with it >> to enforce the referential integrity, as I said in my employee example, >> the delete that SQL server sends will cause a foreign key violation? >> >> >> Regards >> Asser Maany >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >>> If a table is published for replication and updates occurring on it are >>> logged in the tlog as an delete insert pair. The log reader will >>> construct an equivalent update statement from this delete insert pair - >>> unless an update in place cannot occur (ie you are updating a pk amoung >>> other events). >>> >>> I spoke with someone on the query optimizer team about this and he said >>> it was for efficiency in the command construction process (IIRC). I >>> didn't really follow much more of what he said. Is it also possible that >>> you have cascading updates and deletes and you have not checked the >>> enforce constraint for replicatioin option? >>> >>> -- >>> 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 >>> >>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>>> Hi There, >>>> >>>> I was troubleshooting a problem in my transactional replication and >>>> notices that SQL Server replicates some of my update statements as >>>> Delete/Insert pairs, I was really surprised and couldn't understand why >>>> SQL server does that, I found an article in MS support web site >>>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>>> less that this might happen in some cases, still not convinced why, but >>>> this is a fact of life. >>>> >>>> >>>> >>>> Found also another article saying that there is a trace flag that would >>>> force SQL server to perform an update as an update (not Delete/Insert >>>> pair) (http://support.microsoft.com/kb/302341/EN-US/) but still not in >>>> all cases, again failed to understand why. >>>> >>>> >>>> >>>> I'm struggling now to understand why does SQL server do that and also >>>> want a solution from my problem since these delete/insert statements >>>> are conflicting with my database referential integrity, imagine that >>>> I'm updating an employee record that is used in almost all my tables >>>> and has got foreign keys every where referencing it and SQL Server is >>>> deleting this employee when I perform an update on the publisher!! >>>> >>>> >>>> >>>> In the first article they are saying that I should change the logic of >>>> my triggers or replication stored procedures to handle this case, but >>>> how can I detect that the first delete is followed by an insert for the >>>> same record and convert them as an update. >>>> >>>> >>>> >>>> If you faced this situation before or you have a solution for this >>>> dilemma ,please share your experience with me. >>>> >>>> >>>> >>>> Thanks in advance >>>> >>>> Asser Maany >>>> >>>> >>> >>> >> >> > >
Sorry if I'm asking too much questions, but this issue is really getting to
my nerves What do you mean by missing DRI pieces? fill it with what? [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:O2LczSBMGHA.964@tk2msftngp13.phx.gbl... > Yes, if you know the next action will fix it, or fill in the missing DRI > pieces. > > -- > 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 > > "Asser Maany" <asser.maany@microtech.com.eg> wrote in message > news:%23JPmaKBMGHA.532@TK2MSFTNGP15.phx.gbl... >> Possible, but would this be a good practice. >> >> >> >> >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:%23ZV6$HBMGHA.1424@TK2MSFTNGP12.phx.gbl... >>> how about you make the fk's and all constraints Not For Replication? >>> >>> -- >>> 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 >>> >>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>> news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... >>>> Thanks for the fast reply, but my point now is how should I deal with >>>> it to enforce the referential integrity, as I said in my employee >>>> example, the delete that SQL server sends will cause a foreign key >>>> violation? >>>> >>>> >>>> Regards >>>> Asser Maany >>>> >>>> >>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>> news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >>>>> If a table is published for replication and updates occurring on it >>>>> are logged in the tlog as an delete insert pair. The log reader will >>>>> construct an equivalent update statement from this delete insert >>>>> pair - unless an update in place cannot occur (ie you are updating a >>>>> pk amoung other events). >>>>> >>>>> I spoke with someone on the query optimizer team about this and he >>>>> said it was for efficiency in the command construction process (IIRC). >>>>> I didn't really follow much more of what he said. Is it also possible >>>>> that you have cascading updates and deletes and you have not checked >>>>> the enforce constraint for replicatioin option? >>>>> >>>>> -- >>>>> 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 >>>>> >>>>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>>>> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>>>>> Hi There, >>>>>> >>>>>> I was troubleshooting a problem in my transactional replication >>>>>> and notices that SQL Server replicates some of my update statements >>>>>> as Delete/Insert pairs, I was really surprised and couldn't >>>>>> understand why SQL server does that, I found an article in MS support >>>>>> web site http://support.microsoft.com/kb/238254/EN-US/ that is saying >>>>>> more or less that this might happen in some cases, still not >>>>>> convinced why, but this is a fact of life. >>>>>> >>>>>> >>>>>> >>>>>> Found also another article saying that there is a trace flag that >>>>>> would force SQL server to perform an update as an update (not >>>>>> Delete/Insert pair) (http://support.microsoft.com/kb/302341/EN-US/) >>>>>> but still not in all cases, again failed to understand why. >>>>>> >>>>>> >>>>>> >>>>>> I'm struggling now to understand why does SQL server do that and also >>>>>> want a solution from my problem since these delete/insert statements >>>>>> are conflicting with my database referential integrity, imagine that >>>>>> I'm updating an employee record that is used in almost all my tables >>>>>> and has got foreign keys every where referencing it and SQL Server is >>>>>> deleting this employee when I perform an update on the publisher!! >>>>>> >>>>>> >>>>>> >>>>>> In the first article they are saying that I should change the logic >>>>>> of my triggers or replication stored procedures to handle this case, >>>>>> but how can I detect that the first delete is followed by an insert >>>>>> for the same record and convert them as an update. >>>>>> >>>>>> >>>>>> >>>>>> If you faced this situation before or you have a solution for this >>>>>> dilemma ,please share your experience with me. >>>>>> >>>>>> >>>>>> >>>>>> Thanks in advance >>>>>> >>>>>> Asser Maany >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
I couldn't really tell what was the update statement that caused this
delete/insert , but what I can tell you is the data that existed in the table when the foreign key constraint was violated Table when the foreign key was violated BranchID TrxNumber TrxDate DriverID Vehicle Trailer KilometerReading GDNCount BoxCollected BoxTotal Confirmed EntryDate EntryUser BUID NINDCDDC000 26 2/12/06 12:00 AM NULL NULL NULL 0 18 347 347 0 2/12/06 5:04 PM shuhaib Statements that were running causing foreign key violation {CALL sp_MSdel_IC_ShipGDN_Master ('NINDCDDC000 ', '000026 ')} {CALL sp_MSins_IC_ShipGDN_Master ('NINDCDDC000 ', '000026 ', 2006-02-12 00:00:00.000, NULL, NULL, NULL, 0, 18, 347, 347, 0, 2006-02-12 17:04:32.297, 'shuhaib ', ' ')} Schema CREATE TABLE [dbo].[IC_ShipGDN_Master]( [BranchID] [char](15) NOT NULL, [TrxNumber] [char](15) NOT NULL, [TrxDate] [datetime] NOT NULL, [DriverID] [char](15) NULL, [Vehicle] [varchar](15) NULL, [Trailer] [varchar](15) NULL, [KilometerReading] [int] NOT NULL CONSTRAINT [DF__IC_ShipGD__Kilom__4E22BDCE] DEFAULT (0), [GDNCount] [int] NOT NULL CONSTRAINT [DF__IC_ShipGD__GDNCo__4F16E207] DEFAULT (0), [BoxCollected] [int] NOT NULL CONSTRAINT [DF__IC_ShipGD__BoxCo__500B0640] DEFAULT (0), [BoxTotal] [int] NOT NULL CONSTRAINT [DF__IC_ShipGD__BoxTo__50FF2A79] DEFAULT (0), [Confirmed] [tinyint] NOT NULL CONSTRAINT [DF__IC_ShipGD__Confi__51F34EB2] DEFAULT (0), [EntryDate] [datetime] NULL, [EntryUser] [dbo].[BS_User] NULL, [BUID] [varchar](15) NULL CONSTRAINT [DF__IC_ShipGDN__BUID__52E772EB] DEFAULT ([dbo].[ST_FN_GETBU](1)), CONSTRAINT [PK_IC_ShipGDN_Master] PRIMARY KEY CLUSTERED ( [BranchID] ASC, [TrxNumber] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_bindefault @defname=N'[dbo].[DF_Now]', @objname=N'[dbo].[IC_ShipGDN_Master].[EntryDate]' , @futureonly='futureonly' GO EXEC sys.sp_bindefault @defname=N'[dbo].[DF_System_User]', @objname=N'[dbo].[IC_ShipGDN_Master].[EntryUser]' , @futureonly='futureonly' go ALTER TABLE [dbo].[IC_ShipGDN_Master] WITH CHECK ADD CONSTRAINT [FK_IC_ShipGDN_Master__AR_SalesMan__DriverID] FOREIGN KEY([DriverID]) REFERENCES [dbo].[AR_Salesmen] ([SalesmanId]) GO ALTER TABLE [dbo].[IC_ShipGDN_Master] WITH CHECK ADD CONSTRAINT [FK_IC_ShipGDN_Master__ST_Branches__BranchID] FOREIGN KEY([BranchID]) REFERENCES [dbo].[ST_Branches] ([BranchID]) [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:OuytHTBMGHA.2012@TK2MSFTNGP14.phx.gbl... > Can you post the schema of the problem table(s) and also the problem > update delete statements? > > -- > 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 > > "Asser Maany" <asser.maany@microtech.com.eg> wrote in message > news:O0XjZJBMGHA.3408@TK2MSFTNGP12.phx.gbl... >> Sorry, missed your last comments, there are no cascaded deletes or >> updates, and I'm not updating the primary key, that is why I don't really >> understand why is it converted as a delete and update. >> >> >> >> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >> news:ea%23MjDBMGHA.536@TK2MSFTNGP09.phx.gbl... >>> Thanks for the fast reply, but my point now is how should I deal with it >>> to enforce the referential integrity, as I said in my employee example, >>> the delete that SQL server sends will cause a foreign key violation? >>> >>> >>> Regards >>> Asser Maany >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:OgTCD3AMGHA.3100@tk2msftngp13.phx.gbl... >>>> If a table is published for replication and updates occurring on it are >>>> logged in the tlog as an delete insert pair. The log reader will >>>> construct an equivalent update statement from this delete insert pair - >>>> unless an update in place cannot occur (ie you are updating a pk amoung >>>> other events). >>>> >>>> I spoke with someone on the query optimizer team about this and he said >>>> it was for efficiency in the command construction process (IIRC). I >>>> didn't really follow much more of what he said. Is it also possible >>>> that you have cascading updates and deletes and you have not checked >>>> the enforce constraint for replicatioin option? >>>> >>>> -- >>>> 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 >>>> >>>> "Asser Maany" <asser.maany@microtech.com.eg> wrote in message >>>> news:eV2sAbAMGHA.2628@TK2MSFTNGP15.phx.gbl... >>>>> Hi There, >>>>> >>>>> I was troubleshooting a problem in my transactional replication and >>>>> notices that SQL Server replicates some of my update statements as >>>>> Delete/Insert pairs, I was really surprised and couldn't understand >>>>> why SQL server does that, I found an article in MS support web site >>>>> http://support.microsoft.com/kb/238254/EN-US/ that is saying more or >>>>> less that this might happen in some cases, still not convinced why, >>>>> but this is a fact of life. >>>>> >>>>> >>>>> >>>>> Found also another article saying that there is a trace flag that >>>>> would force SQL server to perform an update as an update (not >>>>> Delete/Insert pair) (http://support.microsoft.com/kb/302341/EN-US/) >>>>> but still not in all cases, again failed to understand why. >>>>> >>>>> >>>>> >>>>> I'm struggling now to understand why does SQL server do that and also >>>>> want a solution from my problem since these delete/insert statements >>>>> are conflicting with my database referential integrity, imagine that >>>>> I'm updating an employee record that is used in almost all my tables >>>>> and has got foreign keys every where referencing it and SQL Server is >>>>> deleting this employee when I perform an update on the publisher!! >>>>> >>>>> >>>>> >>>>> In the first article they are saying that I should change the logic of >>>>> my triggers or replication stored procedures to handle this case, but >>>>> how can I detect that the first delete is followed by an insert for >>>>> the same record and convert them as an update. >>>>> >>>>> >>>>> >>>>> If you faced this situation before or you have a solution for this >>>>> dilemma ,please share your experience with me. >>>>> >>>>> >>>>> >>>>> Thanks in advance >>>>> >>>>> Asser Maany >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Hi,
Sorry to break in your discussion like this, but i've got the same problem with replication transforming Update statement into delete/insert statemenst. Situation: when an update occurs on a table in the publisher database, replication transforms this statement into delete/insert statements on the subscriber database. On the table in the subscriber database we have an "after update" trigger that doesn;t fire in this case. According to BOL the transformation of update statements should only happen in case of an update of an unique constraint or unique index column. "If an UPDATE does modify a uniquely constrained column, the UPDATE will be propagated as a series of DELETE statements followed by a series of INSERT statements" We do NOT modify an unique column in the publisher database so i aspect replication should execute an UPDATE statement in my subscriber database, but it doesnt. can someone explain this behavour of sqlserver? thanx in advance,
SHOULD is the operative word. There are a couple of other cases where this
happens, but I can't remember the specifics off the top of my head. This has been an ongoing issue with replication until 2005. I always assume that an update will be propagated as a delete/insert pair. (2005 finds this and correctly propagates an update statement.) What I don't understand is how this is affecting anything other than a foreign key constraint which is taken care of with the NOT FOR REPLICATION option. Why are you having triggers fire when replication sends changes? -- Mike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. "Michel van Marion" <MichelvanMarion@discussions.microsoft.com> wrote in message news:57F447BC-84A9-477D-B3CD-2E6F793F55D7@microsoft.com... [quoted text, click to view] > Hi, > Sorry to break in your discussion like this, but i've got the same problem > with replication transforming Update statement into delete/insert > statemenst. > > Situation: > when an update occurs on a table in the publisher database, replication > transforms this > statement into delete/insert statements on the subscriber database. On the > table in the > subscriber database we have an "after update" trigger that doesn;t fire in > this case. > > According to BOL the transformation of update statements should only > happen > in case of an > update of an unique constraint or unique index column. > > "If an UPDATE does modify a uniquely constrained column, the UPDATE will > be > propagated as > a series of DELETE statements followed by a series of INSERT statements" > > We do NOT modify an unique column in the publisher database so i aspect > replication should > execute an UPDATE statement in my subscriber database, but it doesnt. > > can someone explain this behavour of sqlserver? > > thanx in advance, > Michel van Marion
Michael,
i'll try to explain why we have an after update trigger on the subcriber database table. Our subscriber database is read only to our users because its only a sqlserver database that is a copy of our core Oracle rdb database. We are tracking any modifications in our source rdb database and then copy them to an sqlserverdatabase. From there replication is doing his job to replicate data to other sqlserver databases. The table where we have an after update trigger is called person and holds information about persons. When a person dies a date is filled in a column called date_of_dead. But we have many other applications that want to know that someone died, so we want to publish this event in our Service oriented architecture. the update triggers is collection persons who have died and then create a publication to all our other applications that are also intrested in that event. This is a very brief and inclomplete describtion of how we duplicatie and replicatie data in our compagny. i'll hope you understand my story, because in explaining it in dutch is very difficult, but writing it down in English is even more difficult.
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 |