Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: replicating and update as a delete-insert

    I have a weird problem that has seemingly popped up all of a sudden, and on multiple servers I have set up as publishers. All servers involved are running SQL Server 7.0.

    Basically, the user will perform an UPDATE command on a parent table, but when the command is distributed to the subscriber, it is sent as a DELETE followed by an INSERT. Since I have foreign key constraints at both the publisher and subscriber, the replication fails with a key violation, since it tries to delete a record with children.

    I have checked to ensure that the key constraints are the same at both places, I have even dropped and recreated them all.

    The only thing that I can think of that has changed is that one of the new DBAs has Enterprise Manager for SQL 2000 installed on their workstation, and has been monitoring the replication with it. The publications have all been defined and running normally for several months without these problems, and that's the only change I can think of.

    Any thoughts?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: replicating and update as a delete-insert

    To resolve this problem, simply change your foreign key constraint to "Not for Replication".

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: replicating and update as a delete-insert

    Originally posted by joejcheng
    To resolve this problem, simply change your foreign key constraint to "Not for Replication".
    That did it. Thanks a ton Joe.

    I also figured out why it was happening, and I don't really like what I found.
    I have a unique constraint defined on smalldatetime field and userID field in my offending table, and apparently one of them is getting updated when my error occurs. According to the ms knowledge base, if you update a column that is part of a unique constraint, is will execute the update as a 'deferred update' which really is a DELETE/INSERT pair instead of an update. At the publisher, the deferred update is treated like a regular update, meaning it wont break fkey constraints and it will still use the UPDATE trigger and not the DELETE-INSERT triggers, but the DELETE/INSERT pair are read by the logreader and go into the distribution database instead of the UPDATE. It sounds like this 'deferred update' stuff is a bit of a hack to me. It sounds like their unique constraint checking implementation is flawed, and this was a way around it. It could really cause some obscure problems if you had an UPDATE trigger at a subscriber that did something important. If I send an UPDATE command, I'd expect that same command to be replicated as is, regardless of how it is internally executed. Anyway....


    Here's the article:
    According to Microsoft Knowledge Base Article - 238254:
    http://support.microsoft.com/default...54&Product=sql

    If any column that is part of a unique constraint is updated, then SQL Server implements the update as a "deferred update", which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. There are also other situations that might cause a deferred update. Therefore, any business logic that you implement in your UPDATE triggers or custom stored procedures at the Subscriber should also be included in the DELETE/INSERT triggers or custom stored procedures.

  4. #4
    Join Date
    Jan 2005
    Posts
    2

    Thanks Guys!

    I'm SOOO Glad I found this thread!

    I've been trying to work this bloody Delete foreign key problem out for months now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •