Re: UPDATE Statements May be Replicated as DELETE/INSERT Pairs

Tech-Archive recommends: Fix windows errors by optimizing your registry

Possible, but would this be a good practice.






"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%23ZV6$HBMGHA.1424@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> wrote in message
news:ea%23MjDBMGHA.536@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx> wrote in message
news:OgTCD3AMGHA.3100@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> wrote in message
news:eV2sAbAMGHA.2628@xxxxxxxxxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: Merge Replication - sp_addscriptexec
    ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... running osql not the account running the sql agent service. ...
    (microsoft.public.sqlserver.replication)
  • Re: Basic replication question
    ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Replication - sp_addscriptexec
    ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... The error occurs when trying to apply a script to the subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication between 2 or more SQL Express databases
    ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... Mmm - sounds like we have a different understanding of the term trigger. ... You will probably be able to use any version of SQL Server for this. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 merge repliation over HTTPS/IIS
    ... Contact me offline as I would be interested in helping you with this ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.replication)