Post reply

Rouge Cross Server Update, Updates the Wrong Rows

  • February 10, 2012 at 8:38 am

    #252845

    So here's a little puzzle for a Friday morning...

    We have two servers, one running SQL Server 2005 SP2 and one running SQL Server 2008 SP2. There is a procedure on the 2008 server that updates a status field in a table on the 2005 box incorrectly. The target table is referenced using four part naming and using profiler I can see it is taking a cursor type approach, updating one row at a time.

    I added a trigger to the

    that grabbed the status before (from deleted) and the status after (from deleted) and popped those in a table so I could see which rows had been changed. It is never the same rows, and I don't see a pattern.

    We have tried many tweaks and changes to rule out other processes/triggers/etc messing with the

    , we also added extensive logging throughout the procedure to match the times of the changes with the statement that was running and it points to this guy:

    UPDATE c SET c.status = 'C'

    FROM #CLIST cl

    JOIN [SERVER].[DATABASE].dbo.

    c

    ON cl.id = c.id

    WHERE c.status = 'R' or c.status = 'S'

    After this ran we would see that rows with statuses of 'U','G',etc would be updated and some that should have been updated weren't.

    We found a work around to this by creating a procedure on the 2005 box, that built the temp table and perform the update, and then called that procedure from within the procedure on the 2008 box.

    My question is, has anyone seen this kind of behavior before? Is this a bug that was fixed in a later service pack?

    Any ideas would be great! Thanks 🙂

  • February 11, 2012 at 3:13 am

    #1445870

    About the only time I've seen this problems is when folks looked at the wrong server. Double check where the linked server is pointing to.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 11, 2012 at 7:34 am

    #1445928

    Please crosscheck the synonyms.

  • February 13, 2012 at 10:16 am

    #1446351

    Double checked the linked server and that all looks good.

    Dev (2/11/2012)

    Please crosscheck the synonyms.

    What do you mean by this?

    Thanks for your help

  • February 13, 2012 at 5:41 pm

    #1446592

    jpomfret7 (2/13/2012)

    Double checked the linked server and that all looks good.

    Dev (2/11/2012)

    Please crosscheck the synonyms.

    What do you mean by this?

    Thanks for your help

    Dev means that there may be synonyms for some of the tables/linked servers involved and they could be pointing to the wrong server.

    I think it was already mentioned but also check any triggers you may have on the table(s).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply