Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Sacramento, CA
    Posts
    5

    Angry Unanswered: SQL error when deleting index

    Hi.

    I have a SQL 2000 database. The table's primary key is set up as a "regular" index and another field is set up as a clustered index. When attempting to delete the index on this other field, I am receiving the following error message:

    'tblQuestionAnswer' table
    - Unable to delete index 'tblQuestionAnswer1'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.

    I do not understand why I am receiving this message in relation to the deletion of an index. Any ideas?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: SQL error when deleting index

    Originally posted by LuckyLee

    I do not understand why I am receiving this message in relation to the deletion of an index. Any ideas?
    Just guessing here ....
    [SQL Server Books Online]
    DROP INDEX cannot be specified on an index on a system table.

    To drop the indexes created to implement PRIMARY KEY or UNIQUE constraints, the constraint must be dropped. For more information about dropping constraints, see "ALTER TABLE" in this volume.
    [/SQL Server Books Online]
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jan 2004
    Location
    Sacramento, CA
    Posts
    5

    Unhappy Re: SQL error when deleting index

    Sorry Enigma but that information is not helpful. This is not a system table. Also, there are not any constraints involved. The field involved is not the primary key nor a unique field.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    just do a sp_helpindex on the table and tell me what you get !!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jan 2004
    Location
    Sacramento, CA
    Posts
    5
    Thank you. Below are the results of sp_helpindex

    hind_98099390_2A_1A_3A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, ResponseID, QuestionID
    hind_98099390_2A_3A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, QuestionID
    hind_98099390_2A_3A_1A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, QuestionID, ResponseID
    hind_98099390_2A_3A_4A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, QuestionID, AnswerID
    hind_98099390_2A_3A_4A_5A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, QuestionID, AnswerID, ReasonID
    hind_98099390_2A_4A nonclustered, hypothetical, auto create located on PRIMARY ObservationID, AnswerID
    hind_98099390_3A_4A_5A_2A nonclustered, hypothetical, auto create located on PRIMARY QuestionID, AnswerID, ReasonID, ObservationID
    hind_c_98099390_2A clustered, hypothetical, auto create located on PRIMARY ObservationID
    hind_c_98099390_2A_3A clustered, hypothetical, auto create located on PRIMARY ObservationID, QuestionID
    hind_c_98099390_3A clustered, hypothetical, auto create located on PRIMARY QuestionID
    PK_tblQuestionAnswer nonclustered, unique, primary key located on PRIMARY ResponseID
    tblQuestionAnswer1 clustered located on PRIMARY ObservationID

    The index I am trying to delete is the last one...tblQuestionAnswer1

Posting Permissions

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