One of the steps in my sql proc creates unique index with ignore_dup_key
option. The proc works fine in SQL analyser, although it does display message
3604 - Duplicate key ignored. I use this method to eliminate duplicate rows
during insert.
When this proc is executed from DTS, it terminates abnormally with the same
message 3604.
Has anyone encounter this problem before and would anyone know of a work
around? apart from using another longer method to eliminate duplicate rows
such as create temporary table with identity key.

Thanking in advance.
--
Lily

Lily,

Not sure how to catch that error and stop it, but another method to find
dups is:

SELECT keyColumn(s) , COUNT(*)
FROM theTable
GROUP BY
keyColumns(s)
HAVING COUNT(*) > 1

Check the @@ROWCOUNT after running that.

Norman

get DTS Package Search at http://www.gerasus.com/

LiLy wrote:

Thank you Norman for your reply but I don't know how to apply your solution
to resolve the problem with duplicate rows that I am having. I like to be
able to keep 1 of the duplicate rows. For example I want to keep 1 of the
following rows:

Row 1 has columns A, B, C, D, E
Row 2 has columns A, B, C, D, F
Row 3 has columns A, B, C, G, E

The keys for matching are A, B, C. Since the 3 rows above have the same keys
A, B, C, I like to keep the first row but eliminate the other 2 rows.
By grouping the keys as suggested, I can identify the duplicate rows but how
can I retain 1 of the row?
---
Lily


"Norman Kelm" wrote:

hi lily
other than these columns do u have any columns like GUID or createdate col.
Khwaja Arshaduddin

"LiLy" wrote:

Hi Khwaja,
Unfortunately I don't have any other columns like GUID or CreateDate.
--
Lily


"Khwaja Arshaduddin" wrote: