Duplicate Key Ignored

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

Since you want to avoid inserting rows from the source already existing in the destination, have you tried inserting based on a select that excludes those existing rows?

One way to construct it, is by a LEFT JOIN query.

INSERT destination
( col1, col2 ..... )
SELECT col1, col2 ....
FROM source s
LEFT JOIN
destination d
ON s.pk = d.pk
WHERE d.pk IS NULL

/Kenneth

|||A better way in that case is to use a subselect:

INSERT INTO destination
(col1, col2, ...)
SELECT col1, col2, ...
FROM source
WHERE pk NOT IN (
SELECT pk
FROM destination
)|||

Rick Brown wrote:

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

I have the same problem, when my app runs the stored procedure, the error appears and the data is not retrieved. I don't think I can use the subquery solution, so I would like to get it working with Ignore_Dup_Key.

Isn't the purpose of Ignore_Dup_Key to allow the data to be returned without inserting the duplicated data?

Thanks for your time

Duplicate inserts creating an error

Strange issue (aren't they all). On my VB.Net app, I use merge replication.
Everything appears correct with synchronization and data flows in both
directions. However, I have noticed that on the SQLCE tables where I do
multiple Inserts, I'm now getting an error after the second synchronization.
For example, I do a download of the data, do Inserts on some of the tables,
then merge upload the data to the server. Now I go back for a second
download, and when I try to do an Insert, I get the error, "A duplicate
value cannot be inserted into a unique index".
I'm not trying to insert a primary key or into a primary key field, and I've
deleted all the indexes on any other fields -- except the uniqueID fields
created by the publication creation.
Not sure where I'm going astray here, so any advice would be appreciated.
Which table are you getting this error on? Is it a system table or a user
table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||User tables on the PocketPC.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:opsoxx6ntqrj9kur@.hcottter-lap.ap.org...
> Which table are you getting this error on? Is it a system table or a user
> table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com

DTS using #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperHi
This seems similar to http://tinyurl.com/82fqd
Temporary tables have a limited scope see the section in:
http://msdn.microsoft.com/library/d...r />
_4hk5.asp
Depending on what you are trying to do, you may not require the temporary
table or you could use a permanent table that you clear down before each run
.
Alternatively using a derived table or use of the CASE statement may be
possible options.
John
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>

DTS Trimming My Data

Hi

I have a DTS package that pulls data from oracle and inserts it into SQL.
During this transfer, any data that has trailing spaces, loses those spaces
in SQL. i.e. it's been trimmed.

Any way to set DTS not to trim the data ?

Thanks

SteveHi

This may be due to the ANSI_PADDING setting being OFF

http://msdn.microsoft.com/library/d...asp?frame=true

John

"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bj4qt9$752$1@.titan.btinternet.com...
> Hi
> I have a DTS package that pulls data from oracle and inserts it into SQL.
> During this transfer, any data that has trailing spaces, loses those
spaces
> in SQL. i.e. it's been trimmed.
> Any way to set DTS not to trim the data ?
> Thanks
> Steve