Rookie Question - DTS & return/exit codes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Rookie Question - DTS & return/exit codes

Author

Topic

DBjellqu
Starting Member

2 Posts

Posted - 2008-01-15 : 17:49:42

A DTS Package may contain multiple steps. In one of these steps, I included a SQL statement as follows:

insert into table2
select * from table1

table1 contains rows, which have fields with non-unique values.
table2 has been defined with the same structure as table 1. It has unique indexes on the corresponding fields with the ignore_dup_key option, which will allow the first row in, but disregard subsequent rows that have same value.

This is purposely being done as a way to 'clean up' the data - and it works functionally. The index is doing the work of removing duplicates.

However, in stripping away undesired rows it generates:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Duplicate key was ignored

This causes the DTS step and the entire DTS Package to generate an Error.

How can I get a 'clean run' out of this? I want to totally 'ignore' the error, or override the exit/return code in some manner. Is this possible?

DBjellqu
Starting Member

2 Posts

Posted - 2008-01-19 : 18:56:25

By temporarily inserting my code:

insert into table2
select * from table1

into a stored procedure, and running it, I can get more information on the error.

Server: Msg 3604, Level 16, State 1
Duplicate key was ignored.

I find lots of threads on this 'infamous' Msg 3604 error, but not lots of happy answers.

I've even read somewhere that this may have been a bug, corrected in SP1. I have:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

I am trying to do the insert in a 'Execute SQL Task' in a 'DTS Package'.

Any suggestions, or is this not solvable?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 20:11:02

if u want to filter out dupes, u need to do that in your query. the constraint wont do it for u, just cause an error

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-21 : 06:38:41

have you had a go at

insert into table2
select distinct col1, col2, col3, col4, etc..... from table1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources