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

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@xxxxxxxxxxxxxxxx> wrote in message
news:%23JPmaKBMGHA.532@xxxxxxxxxxxxxxxxxxxxxxx
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: Single point of failure
    ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.replication)
  • Re: Single point of failure
    ... "Hilary Cotter" wrote: ... Looking for a SQL Server replication book? ... publisher and subscriber to each other and to the other publishers ...
    (microsoft.public.sqlserver.replication)
  • Re: Article Defaults --> cant change
    ... >> Hilary Cotter ... >> Looking for a SQL Server replication book? ... >>> We don't want data deleted on the subscribers when data is deleted on ...
    (microsoft.public.sqlserver.replication)
  • Re: Document properties
    ... Hilary Cotter ... Do we know if this problem will be addressed in SQL Server ... So your query would look like this: ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: license Question - Please Help
    ... "Hilary Cotter" wrote: ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading