Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2006
    Posts
    74

    Unanswered: Unable to re create index

    Hi

    I was asked to import some data from excel into a table within a sqlserver 2000 db the import was complaining about an index so then I deleted the index imported the data succesfilly but I'm now unable to re create the index, please see the error that I'm getting below. Can someone please help.
    Thanks

    - Unable to create index 'trainingGo'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 32. Most significant primary key is '439'.
    [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Does 32 appear twice?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Posts
    74
    Not that I can see, there was only one index on that table and thats the one I'm unable to recreate

  4. #4
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    As the error message suggests, check for duplicates, clean up the data, and then try recreating the index.

    -a

  5. #5
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Post the create index code.

  6. #6
    Join Date
    Jan 2006
    Posts
    74
    I dont have the sql I used the design tool on sqlserver to try and recreate it. Indexes/keys and selected the new button.

  7. #7
    Join Date
    Jan 2006
    Posts
    74
    CREATE UNIQUE
    INDEX [TRAININGO] ON [HR].[TRAINING] ([Staff_No], [Start_Date], [End_Date], [Course])
    ON [PRIMARY]

  8. #8
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Run this, and post the results:

    select [Staff_No], [Start_Date], [End_Date], [Course], count(*)
    from [HR].[TRAINING]
    group by [Staff_No], [Start_Date], [End_Date], [Course]
    having count(*)>1

  9. #9
    Join Date
    Jan 2006
    Posts
    74
    439.0 2005-09-22 00:00:00.000 2005-09-22 00:00:00.000 Data Protection Act 2
    559.0 2006-02-21 00:00:00.000 2006-02-21 00:00:00.000 Bullying & Harassment at the Workplace 2
    2511.0 2005-09-23 00:00:00.000 2005-09-23 00:00:00.000 Data Protection Act 2
    3255.0 2007-01-17 00:00:00.000 2007-01-17 00:00:00.000 Fire Warden 2
    3704.0 2005-09-22 00:00:00.000 2005-09-22 00:00:00.000 Data Protection Act 2
    3892.0 2005-11-03 00:00:00.000 2005-11-03 00:00:00.000 Fire Warden 2
    4263.0 2005-09-23 00:00:00.000 2005-09-23 00:00:00.000 Data Protection Act 2
    4629.0 2006-04-06 00:00:00.000 2006-04-06 00:00:00.000 Bullying & Harassment at the Workplace 2
    4661.0 2005-09-22 00:00:00.000 2005-09-22 00:00:00.000 Data Protection Act 2
    4828.0 2006-09-27 00:00:00.000 2006-09-27 00:00:00.000 Bullying & Harassment at the Workplace 2
    4842.0 2007-04-17 00:00:00.000 2007-04-17 00:00:00.000 Bullying & Harassment at the Workplace 2
    4861.0 2006-11-01 00:00:00.000 2006-11-01 00:00:00.000 Bullying & Harassment at the Workplace 2
    4884.0 2007-02-16 00:00:00.000 2007-02-16 00:00:00.000 Laboratory Safety Induction 2

  10. #10
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Those are your dups. Clean up the data and then try to create the index. Should work.

    -a

  11. #11
    Join Date
    Jan 2006
    Posts
    74
    Thanks I will give it ago.

  12. #12
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Is it possible that you imported your data twice into the table,
    I'm asking because all of your COUNT(*) returns 2.

    Maybe I'm wrong but it looks like it.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  13. #13
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by ortho
    Is it possible that you imported your data twice into the table,
    I'm asking because all of your COUNT(*) returns 2.

    Maybe I'm wrong but it looks like it.
    Unlikely, unless the table is supposed to have only 13 records.

    -a

  14. #14
    Join Date
    Jan 2006
    Posts
    74
    The table has over 5000 rows.

  15. #15
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by architect
    Unlikely, unless the table is supposed to have only 13 records.

    -a
    oops my mystake i didn't see the having clause..

    Last edited by ortho; 10-22-07 at 14:15.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

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