MS SQL Server - UPDATE Statements May be Replicated as DELETE/INSERT Pairs

Author

UPDATE Statements May be Replicated as DELETE/INSERT Pairs

Asser Maany

2006-02-12, 1:23 pm

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

Hilary Cotter

2006-02-12, 8:23 pm

Please don't cross post. I answered you in the replication newsgroup.

--
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:uu3KSfAMGHA.3408@TK2MSFTNGP12.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
>
>
>

Sponsored Links