Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Posts
    16

    Angry Unanswered: Need Help 1505 Error

    Hello All
    I have a SQL 6.5 Server running on Service Pack 5a with a hotfix.
    select @@version returns:
    Microsoft SQL Server 6.50 - 6.50.451 (Intel X86)
    Jul 1 1999 01:11:25
    Copyright (c) 1988-1997 Microsoft Corporation

    I am getting an error something like this:
    Error 1505 Create unique index aborted on duplicate key. Primary key is '847474 '

    When this is happening is after copying some data and during the creation of an index. I have 2 tables one called ITEM_INVENTORY the other ITEM_INVENTORY_WORK. The only difference between them is that the WORK table has an identity column.

    I use a stored procedure to perform the following steps:
    1. Truncate the table ITEM_INVENTORY
    2. Drop all the indexes on ITEM_INVENTORY
    3. Copy the data from the ITEM_INVENTORY_WORK table to the ITEM_INVENTORY table in 10000 row chunks using:
    BEGIN TRAN
    INSERT INTO ITEM_INVENTORY
    SELECT <(COLUMN LIST)>
    FROM ITEM_INVENTORY_WORK
    COMMIT TRAN
    4. Recreate all the indexes.

    During step 4 (recreate indexes) the process fails and returns a 1505 error while creating the UNIQUE index: CREATE UNIQUE INDEX ITEM_INVENTORY_idx1 ON dbo.ITEM_INVENTORY(IC_ITEM_NO, CORP_CODE)

    Now here is the kicker. There are no duplicates. I've checked and re-cheked and there are no duplicate records. I think this may be a bug, but I don't know. Has anyone encountered this problem and have any ideas on how to solve it?

  2. #2
    Join Date
    May 2002
    Location
    New York
    Posts
    35
    It's not about duplicare records - it's about duplicate IC_ITEM_NO, CORP_CODE.

    Run this:

    select IC_ITEM_NO, CORP_CODE
    from ITEM_INVENTORY
    group by IC_ITEM_NO, CORP_CODE
    having count(*) > 1

    See what It returns. There are no miracles

  3. #3
    Join Date
    Jun 2002
    Posts
    16

    Thumbs down No dups found

    Hi I ran the query:
    select IC_ITEM_NO, CORP_CODE
    from ITEM_INVENTORY
    group by IC_ITEM_NO, CORP_CODE
    having count(*) > 1


    and I get nothing back. Like I said no duplicates.
    The other crazy thing is the WORK table is truncated and recreated each day in a similar way but it does not error when recreating the index.

    Regards
    CaptainEstock

  4. #4
    Join Date
    May 2002
    Location
    New York
    Posts
    35
    Try this:
    drop table ITEM_INVENTORY
    select * into WORK

    drop IDENTITY column
    recrate indexes

    Do not forget to set DB option to allow Bulk copy/select into

  5. #5
    Join Date
    May 2002
    Location
    New York
    Posts
    35
    Sorry,

    select * into item_inventory
    from work

  6. #6
    Join Date
    Jun 2002
    Posts
    16

    Question hmmmmm

    I can't drop columns in SQL 6.5

    I might be able to do a SELECT with the column names...

Posting Permissions

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