trapping failing index rows, or source

Question

trapping failing index rows, or source

drew.georgopulos

SSCarpal Tunnel

Points: 4931
More actions
October 14, 2015 at 9:23 am

#399183

that violates the targets referential integrity?
I am getting error Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object XXX with unique index YYY.
The statement has been terminated.
I would like to know if there is a way to examine or determine what source rows are not conforming to the unique index.
I'm fine with dropping and reestablishing the index, and i know its cataloged somewhere because during index creation, the error message does tell you the row details clobbering index creation. Ideally i would like to be able to trap all the failing rows and see what i can do about rehabilitating them or ignoring them or managing them some other way, but id like to know what the server knows when it will not create the index
thanks a lot

Viewing 3 posts - 1 through 2 (of 2 total)

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

djj SSCoach Points: 18831 More actions · Answer 1

drew.georgopulos (10/14/2015)
that violates the targets referential integrity?
I am getting error Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object XXX with unique index YYY.
The statement has been terminated.
I would like to know if there is a way to examine or determine what source rows are not conforming to the unique index.
I'm fine with dropping and reestablishing the index, and i know its cataloged somewhere because during index creation, the error message does tell you the row details clobbering index creation. Ideally i would like to be able to trap all the failing rows and see what i can do about rehabilitating them or ignoring them or managing them some other way, but id like to know what the server knows when it will not create the index
thanks a lot

Run a query on the data to count the number of values. Say your key is XYZ

SELECT XYZ, COUNT(*) FROM MyTable GROUP BY XYZ HAVING COUNT(*) > 1;Then use this information to find the records causing the problem.

Edit: will not let me use the greater than symbol

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 2

Thanks very much.

I'm doing something like that now, except with ROW_NUMBER() and partitioning by the whole field list to find row numbers bigger than 1.

But I was hoping to be able to invoke and trap the same error we get on index creation that fails on a duplicate row...that one tells you the values that are bad.

Thanks again