Post reply

Rebuild SQL index fails in SQL 6.5

  • February 14, 2006 at 9:25 am

    #111899

    After a failed upgrade attempt, I investigated the problem and found that even though the index already existed on sql 6.5, when I try to rebuild it, I get "Error 1505 - Create unique index aborted on duplicate key.  Primary key is 250."

    If I uncheck unique keys, it works, but unique keys WAS checked on the original index, before the rebuild attempt.

    How can I find and remove these duplicate keys?

    Thanks!

    - JB

     

     

  • February 14, 2006 at 11:33 am

    #620788

    group by <key> having count(*) > 1. This will find you the duplicate keys and then you can delete them.

     

    Kindest Regards,

    Amit Lohia

  • February 14, 2006 at 12:38 pm

    #620807

    I think I get it, but maybe need a little more info....

    Since the key for this index is TicketID for the Ticket table, I tried the query:

    select * from Ticket

    group by TicketID having count(*) > 1

     

    This returned a bunch of errors like this:

    Msg 8120, Level 16, State 1

    Column 'Ticket.FolderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    I probably don't want to do it as a select *, do I?

     

    - JB

     

  • February 14, 2006 at 2:03 pm

    #620824

    Ok, I think I get it now.

    I issued "select TicketID from Ticket group by TicketID having count(*) > 1"

    and this came back with

    TicketID   

    -----------

    301068226

     

    as a result.  I am assuming this is the duplicate, but how do I find and remove it based on this result?

    Thanks!

    - JB

     

  • February 14, 2006 at 2:12 pm

    #620829

    move the rows into temp table where ticketid=301068226

    delete from the ticket table where ticketid=301068226

    Insert the row which u want in the table...

     

    -krishnan

    -Krishnan

  • February 15, 2006 at 8:21 am

    #621064

    I will give it a shot.

    Thanks!

    - JB

     

  • February 15, 2006 at 9:25 am

    #621085

    Getting close, but it is still fighting me.... since it's just one ticket, we can just kill the rows and re-enter the ticket from the app, so I am trying to just do the delete.

    When I issue the "delete from Ticket where TicketID=301068226" query, I get this message back:

    Msg 644, Level 21, State 1

    The non_clustered leaf row entry for page 1456296 row 16 was not found in index page 446266 indexid 4 database 'WINLANCE'

    Once this delete works, I think we will be OK.

    - JB

     

  • February 15, 2006 at 9:33 am

    #621088

    Looks like I figured it out.  When I first deleted all the indexes on the Ticket table, then issued the delete query, it worked!

    I can now rebuild all the indexes.

    Thanks for all your help!

    - JB

     

Viewing 8 posts - 1 through 7 (of 7 total)

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