1

I have a script that gives an error when being executed:

Msg 1505, Level 16, State 1, Server CBR07I300FVA1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 17. Most significant primary key is '44'. The statement has been terminated.

The script contains thousands of lines of queries so I have no idea where the error comes from in the script. Is there a way to know what "index ID 17" stands for?

Improve this question
5
  • 1
    It tells you: "most significant primary key is: 44" - so search for your row with pk = 44 ...... Commented Sep 22, 2010 at 7:23
  • the script is comprised of many queries. I don't know which table it refers to. How can I search for a row? Commented Sep 22, 2010 at 7:32
  • do you have any part in there that has a CREATE UNIQUE INDEX ?? If so: that's most likely your culprit - what table and what columns does it try to create a UNIQUE INDEX on ?? That's your problem - find it, fix it - done! Commented Sep 22, 2010 at 7:50
  • If it's so easy I don't need to raise a question here. It takes more than 15 minutes to run the whole thing, other than that I have to restore the database every time I test. There are more than 20 places that use CREATE UNIQUE INDEX, where none of them refers to the same table. I just want to know the answer to quickly identify where the error is coming from. I was thinking the index ID 17 must mean something otherwise why use an ID here if it is meaningless? Commented Sep 22, 2010 at 23:44
  • Since the question was never directly answered... index IDs are only relative to a particular table. So, table A can have indexes 0, 2, 3, and 7; and table B can have 0, 2, 3, and 4. Given just index ID 2, that's not enough to identify the specific index since you still need to know which table it's on. Commented Jul 1, 2011 at 19:12

4 Answers 4

Reset to default
1

Insert print statement before every significant step (say, create unique index) in the script and you're done.

It's usually done like this:

if @@error <> 0
   PRINT '@@error is ' + ltrim(str(@@error)) + '.'
else
   print 'Index IX_... successfully created'
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you Denis. That is an option but it still requires to roll back all the changes and test again. Besides, I will need to remove that statement for each significant step once the cause is identified.
@newguy Why would you want to remove it?
No idea. I haven't done that before. I think it might take more time to produce the output so what if I run 20 of the similar scripts every day?
@newguy Unless you put this print statement in a while loop with millions of iterations there's no harm done.
If every query starts with "CREATE UNIQUE INDEX" uses this statement then they will all look the same when an error occurs. I am still not able to know where the error is from. I'll probably need to count the number of the "CREATE UNIQUE INDEX" queries and count to see what position does the error prints out. So I don't think this is a very good idea. Is there any better way?
|
1

You say a script with thousands of lines, eh?

My advise: put a print("Test") in the middle and see wether the error occurs before or after. And then again in the middle of the middle etc. until you find the place that is causing you the troubles.

Improve this answer

Comments

0

The table you're working on already contains data; and the data isn't unique with regard to your new index.

Example:

 col1 | col2 | col3
====================
 foo  | 1    | q
 bar  | 2    | w
 bar  | 3    | e
 bar  | 2    | r

In the above table, you couldn't create a unique index on (col1,col2), exactly because the data in it would be non-unique (multiple rows with (bar,2)). The script can't know which of those "duplicate" rows is actually needed. There are three options available to it:

  • create a UNIQUE index with duplicate rows (invalid, as it's not unique any more)
  • delete the duplicate rows (unsafe, how can it know which rows are needed?)
  • do nothing and throw an error (safest option, you are here)

What you can do to resolve this:

run a query to find duplicates - if you group the rows by those columns used by the index, some of the groups will have multiple rows. Those are your duplicates; you need to somehow eliminate their duplicity.

Improve this answer

2 Comments

Yes I know. The problem is I don't know which table and which index the error is referring to.
@newguy: I have misunderstood the question then. Sorry.
0

If you are running this script in SSMS, just double click on the error and it will take you to the line of code that has caused the error...

Improve this answer

2 Comments

It says Line 1 right in the message, so it's probably a dynamic sql thing.
IIRC, a GO causes the line number to reset back to 1, so this should still work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.