SQL Server Performance

Phantom row issue...

Discussion in 'SQL Server 2005 General Developer Questions' started by gcetti, Dec 1, 2010.

  1. gcetti New Member

    How are phantom rows resolved? I have a row where it displays the record in the data view, but does not allow me to update/delete the row - giving the error: "The updated row has been changed or deleted since data was last retrieved".
    I also get varying results based on the query I use:
    Select * from MyTable Where ID=123 - returns 0 records
    Select * from MyTable Where LastName='Smith' - returns 1 record, containing ID=123
    I have run DBCC CHECKDB/CHECKTABLE with REPAIR_ALLOW_DATA_LOSS, but it does not correct the issue.
    What can I do with this record - how can I delete it?
  2. Adriaan New Member

    Have you closed and re-opened the connection?
    Is ID a unique key to MyTable?
    Does the actual query involve more than one table, and at least two tables have a column called ID, and you did not qualify the column reference with the table name (or its alias)?
  3. gcetti New Member

    Nothing fancy to this table - ID is a unique key (of data type INT), and they query is only selecting from the single table.
    The connection also has been closed & reopened (the actual issue exists on a customer site but bringing a backup of their data to my computer I can reproduce the same situation after restoring their file).
  4. Adriaan New Member

    If there is a delete trigger, try disabling it and repeat the delete. You can do a silent roll-back in a trigger. Note that if the trigger contains an action query against another table, then a roll-back on that last action would also roll-back the initial delete on MyTable.
    If there is any FK constraint on the table, or referring to this table, you could try dropping them one-by-one, and repeat the delete, to see what is blocking the delete. If that is the case, then you've been ignoring error messages (or your client app is not telling you about them).
  5. jdgonzalez New Member

    What is the data type of the ID column? I'm wondering if the ID column is padding some spaces.

Share This Page