Any suggestions please?

  • Thomas Cavanaugh

    Grasshopper

    Points: 19

    I have a table in SQL Server 7. This table has no key field defined. I have always been able to delete records from this table before. Now when I try to delete a record I get this message: "Key column information is insufficient or incorrect. Too many rows were affected by update."  There is no key column and deleting this record shouldn't orphan any records. Any ideas?

     

    Thanks

    Tom Cavanauigh

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Maybe there was a unique index that's been deleted.

    Or did you change the context of the delete (likw now using access when the old app was in vb)?

  • Thomas Cavanaugh

    Grasshopper

    Points: 19

    There never should have been any unique indexes or unique keys for this table. This table is the many in a one to many relationship. We have always used VB for the application associated with this DB. How could I tell if someone used Access instead?

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    You'd have to run the profiler.

    The fact is that something somewhere in the application or server changed (or was flawed from design and just now is breaking). You have to find what's changed.

  • Paul Cresham

    Hall of Fame

    Points: 3621

    By what method are you trying to perform the deletion? From within Enterprise Manager...?

    If you're trying to delete a duplicate row from within EM then you will always get this error.

  • sushila

    SSC-Dedicated

    Points: 35293

    Have you tried running an sp_depends against the table to see if anything's changed ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Checking for missing foreign keys??

  • Paul Cresham

    Hall of Fame

    Points: 3621

    Sounds more to me like the rows deleted in the past have not been duplicates, and this one is, rather than being that anything has changed.

    This is an Enterprise Manager (or provider?) error not a SQL Server one because you've chosen to delete one of a duplicate row. You wouldn't get this from a SQL statement. Because they are identical it cannot differentiate between the two and thus cannot delete just one of the rows - it has to delete all or nothing.

    Check this out (second question) or Google on the message.

    You can also get the error using a Recordset object in ADO - similar scenario. I've never seen another reason for this error, though I'm open to surprises.

  • JP de Jong-202059

    SSCommitted

    Points: 1674

     

    Every table needs a unique key! In my experience, I always got this message when a developer forgot to implement unique indexes on a table.
     
    I suggest start reading articles on database design and normalization. (1NF, 2NF and 3NF...)

    JP

  • Roy Rogers

    SSCertifiable

    Points: 7712

    I agree with paul. This error will come when we try delete duplicate rows with enterprise manager. Entire row is a duplicate. Since there is no key specified for this table the duplicate rows can be deleted in Query Analyser.

    (Unless you worked with a bad database design you woul never have faced this problem. Many experts in this forum never have faced this problem since they should have designed a table always with a unique key. Which is a rule to create a table)

    SELECT DISTINCT * FROM TableName INTO #MyRow FROM TableName WHERE column1 = value and Column2 = value Column3 = value (Should mention for all columns available since no primary, unique key or timestamp is in the table)

    delete from TableName where column1 = value and Column2 = value Column3 = value(Should mention for all columns available since no primary, unique key or timestamp is in the table)

    INSERT TableName SELECT * FROM #MyRow --GET your one row back in the table

    Regards,
    gova

  • Thomas Cavanaugh

    Grasshopper

    Points: 19

    The rows I was trying to delete from Enterprise Manager were duplicates. After reading some of the posts, I deleted all of the duplicates and the orginal using query analyzer. I then just re entered the original. Thank you all for the help!

  • Paul Cresham

    Hall of Fame

    Points: 3621

    Would this table not benefit from a primary key? As already said, it should have one anyway, and if you're trying to avoid duplicates then that speaks for itself.

  • SueB

    SSCommitted

    Points: 1537

    When just needing a quick fix I have created a identity field, deleted the duplicate record and then deleted the identity field.

     

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    Hi,

    If the problem is still existing ,

    do the following.

    As you might have got that its coming because of duplicate records. so you need to correct the same by removing the duplicate records from this table.

    try this,

    select fld1,fld2,fldn from table group by fld1,fld2,fldn having count(*) > 1

    this will return all the duplcate records

    now run a loop against this records for delete. before use thsi command

    set rowcount 1

    delete the records where fld1 = @value and fld2 = @value

    set RowCount 0

    this rowcount stuff will make sure that you are deleting only one record.

    run the looptill the query dont return any rows.

    select fld1,fld2,fldn from table group by fld1,fld2,fldn having count(*) > 1

    pls let me know if this solves teh problem ort you want the same in detail.

    Jeswanth

     

    --------------------------------

Viewing 14 posts - 1 through 14 (of 14 total)

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