We are using a dynamically created DTS package to do a complete push
replication from our production system to our public-facing website.

Recently, our DTS package has started to throw errors like to the
following (Culled from the DTS DataPump log, obviously)

----
Execution Started: 6/9/2005 8:12:15 AM
Error at Destination for Row number 25068. Errors encountered so far in
this task: 1.

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:The statement has been terminated.
Error Help File:
Error Help Context ID:0


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Violation of PRIMARY KEY constraint 'PK_Policy'.
Cannot insert duplicate key in object 'Policy'.
Error Help File:
Error Help Context ID:0
----

The destination table is truncated in the prior step, before the
datapump task executes. The source table is clean (no duplicate PKs).
The primary key column is proc-generated rather than Identity(1,1) not
null.

The DataPump task properties have not been changed, same database
names, same schemas, etc. The DataPump task is set to use fast load,
with a max error count of 0.

When you say Replication do you mean Replication in SQL Server and this
package acts as the transport mechanism or that you use DTS to replicate
data to another server?

Can you narrow down the row that throws the error?
If the source is clean and the destination has no rows then is it possible
two processes have generated the same value?



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Jon Choy" <jchoy (AT) sgccomputer (DOT) com> wrote

isn't 1805 The CREATE DATABASE process is allocating %.2f MB on disk
'%.*ls'?

what happens when you issue a xp_cmdshell 'dir'

--
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
"Jon Choy" <jchoy (AT) sgccomputer (DOT) com> wrote

There is a foxpro 7 app that runs as a scheduled task on the database
server, builds a DTS package in COM interop, and then executes it. I've
built a debug version of the app that saves the DTS package to file for
examination, and the properties of the failing task are all consistent
between executions that succeed and executions that fail.

The row that throws the error varies between runs of the job. The app
running the website which the destination database drives is halted
under the control of this process, the source database COULD be touched
by users during execution. The source database tables all have the same
PK constraints, and use the same stored procedure based PK generation
to allocate IDs. The source database is not locked, but the errors are
thrown well before the end of whichever table the process halts on.

The runtime until error has been varying as well.

The question I'd have is if anyone else has investigated similar
problems far enough to determine what kind of network issues can make a
DTS DataPump package 'hiccup' and double-insert a row? I saw the below
article about someone experiencing an apparently similar problem, but
no resolution on a cause, just a manual fix.

Differences that I can see in the scenario I'm facing: SQL 2000 sp 3
both ends, 2003 server on source, 2000 server destination, not
clustered on either end.

Should I recommend that the network admin side of our shop get with PSS
to troubleshoot this, or does anyone have a suggestion (other than
switching to a scheme where SQL Server Replication can do the job,
rather than relying on a client code app to fake it - our upper
management has dictated No Replication for the foreseeable future for a
variety of reasons)

I'm not sure what you're asking, Hilary... I'm not seeing a reference
to an error 1805 in my earlier message?

--
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
"Jon Choy" <jchoy (AT) sgccomputer (DOT) com> wrote

--
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
"Jon Choy" <jchoy (AT) sgccomputer (DOT) com> wrote

Oops sorry I answered the wrong question.

--
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
"Jon Choy" <jchoy (AT) sgccomputer (DOT) com> wrote

Update on this job -

The failure is reported in today's log at row 25070.

Select count (PK) from (failing table) reports 25069 rows.

Why would DTS insert a row twice?