Post reply

Any suggestions please?

  • June 14, 2005 at 7:57 am

    #87977

    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

     

  • June 14, 2005 at 8:09 am

    #565323

    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)?

  • June 14, 2005 at 8:26 am

    #565332

    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?

  • June 14, 2005 at 8:28 am

    #565337

    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.

  • June 14, 2005 at 8:55 am

    #565358

    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.

  • June 14, 2005 at 9:24 am

    #565373

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







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

  • June 14, 2005 at 9:29 am

    #565376

    Checking for missing foreign keys??

  • June 14, 2005 at 9:36 am

    #565382

    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.

  • June 15, 2005 at 1:56 am

    #565570

     

    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

  • June 15, 2005 at 6:50 am

    #565650

    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

  • June 15, 2005 at 7:31 am

    #565682

    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!

  • June 15, 2005 at 7:34 am

    #565683

    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.

  • June 15, 2005 at 2:51 pm

    #565914

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

     

  • June 16, 2005 at 6:18 am

    #566040

    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 13 (of 13 total)

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