sql server dts:
Trying to upload some data from our SQL Server to our AS/400 via a DTS
package and am receiving an error I have not encountered nor even seen
before:
"The number of failing rows exceeds the maximum specified.
Query cannot be updated because it contains no searchable columns to use as
a key."
The DTS package has a connection object for the SQL Server (source) and
another for the AS/400 (destination) with a Transform Data task between
them. The Transform task has a simple SELECT query pulling data from a
single table, and has a destination set to a table on the 400, where the
tables on both sides are identical so the task just copies the columns
straight across.
I created this on one SQL Server, and it ran fine. When I created it on a
second SQL Server (same everything) and run it, I get the above error. (I
am trying to move all our stuff from one SQL Server to another, that's why
the two servers)
A quick search produced:
MSKB #326166
(
http://support.microsoft.com/default.aspx?scid=kb;en-us;326166)
PRB: "Query Cannot Be Updated" Error Message with OLE DB Provider for
ODBC Drivers
From the article it sounds like the error is caused due to ambiguous column
data where the server can't tell one row from another. However, this is most
certainly not the case. There are many columns that are unique to each
record and also unique within the returned records.
I am using the "iSeries Access ODBC Driver" (not sure how to tell what
version or other driver specific info) through a System DSN that I then use
in the DTS package. I have checked, double checked, and re-checked that the
DSN, DTS, tables, and SQL queries are all same same same between the "old"
sql box and the "new" sql box. I brought your tip to our AS/400
administrator and it wasn't ringing any bells for him. And with the help of
a network guru here have verified there is no connectivity issues.
Inside the Transform Data Task there is the option to provide an "Exception
File". In hopes this would shed a clue, I gave it one, but now have only
more questions. This is the output of that text file after a DTS run:
****************************************************************************************************
Execution Started: 5/2/2006 1:39:05 PM
Error at Destination for Row number 1. Errors encountered so far in this
task: 1.
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description:Query cannot be updated because it contains no searchable
columns to use as a key.
Error Help File:
Error Help Context ID:0
1|1360157|rsullivan@healthchoiceaz.com| | ||0|0||N|||||0|0||FLastName
|3/27/2006 9:01:00 AM|HCG Web |3/27/2006 9:02:00 AM|
1|1360157|rsullivan@healthchoiceaz.com| | ||0|0||N|||||0|0||FLastName
|3/27/2006|HCG Web |3/27/2006|
Execution Completed: 5/2/2006 1:39:05 PM
****************************************************************************************************
Now, if you look, it shows two identical rows, but when I look at the SQL
Server table there is only one row with that info. The AS/400 destination
table is empty.
So now it seems I have two questions:
1) Why am I unable to upload the records, and getting the error stated above
when trying?
2) Why does the "exception" file show two identical records?
WFT??
-- Andrew
