The number of failing rows exceeds the maximum specified. Unspecified error

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jay via S » Wed, 31 Oct 2007 00:45:53


I received the following error on a failed DTS package 'The number of failing
rows exceeds the maximum specified. Unspecified error'.

I found the following error in the package log:
'Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The statement has been terminated. (Microsoft OLE DB
Provider for SQL Server (80004005): Subquery returned more than 1 value. This
is not permitted when the subquery follows =, !=, <, <= , >, >= or when the
subquery is used as an expression.)
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0'

I increased the Maximum allowed errors to 9999 to get the package to run
successfully. Is there anyway to find the errors that were creating the
package to fail?

Thanks,

--
Message posted via http://www.yqcomputer.com/
 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jhofmey » Wed, 31 Oct 2007 01:05:07


Hi Jay,

returning multiple values for some rows. This could occur in a number
of places, so it's not really possible to debug your code unless you
post your SQL statement.

Typically, this would occur if you're doing something like:

SELECT col1, (SELECT t2.col2 FROM table2 t2 WHERE t2.col1 = t1.col1)
as col2
FROM table1 t1

Or:

SELECT col1, col2
FROM table1 t1
WHERE col2 = (SELECT t2.col2 FROM table2 t2 WHERE t2.col1 = t1.col1)

If the table2 query returns 2 rows, these statements will fail.

Good luck!
J

 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jay via SQ » Wed, 31 Oct 2007 01:38:39

J,

Here is the sub query. Any addtional advice is most welcomed.

SELECT dbo.PROVSTAT.PTSTAT_ID,
dbo.PROVSTAT.PROV_ID,
dbo.PROVSTAT.EFF_DATE,
dbo.PROVSTAT.EXP_DATE,
dbo.PSTATUS.DESCRIPT,
dbo.PROVSTAT.REASON,
dbo.PROVSTAT.TOUCH_DATE,
dbo.PROVSTAT.TOUCH_BY,
dbo.PROVIDER.NON_PAR
FROM dbo.PROVSTAT
INNER JOIN dbo.PSTATUS ON dbo.PROVSTAT.STATUS = dbo.PSTATUS.
STATUS
INNER JOIN dbo.PROVIDER ON dbo.PROVSTAT.PROV_ID = dbo.PROVIDER.
PROV_ID



--
Message posted via SQLMonster.com
http://www.yqcomputer.com/
 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jhofmey » Wed, 31 Oct 2007 02:48:18


Hi Jay,

Is this the full query? If so, I can't see any subquery that might
cause this error... INNER JOIN's could result in multiple rows being
generated, however this would not fail the Source task.

Regards,
J
 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jpettigrew » Wed, 31 Oct 2007 04:09:19

Sorry, I am still new to this.

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT Provider.Provider_Key FROM dbo.Provider WHERE
Provider.Datasource_ID = FEIProviderStatus.DatasourceID)

UPDATE dbo.FEIProviderStatus
SET PayorKey = (SELECT Payor.Payor_Key FROM dbo.Payor WHERE Payor.
Datasource_ID = FEIProviderStatus.DatasourceID)

I just executed this part of the pacakge and got the error.





--
Message posted via SQLMonster.com
http://www.yqcomputer.com/
 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by jhofmey » Wed, 31 Oct 2007 08:17:15


Hi Jay,

Those statements look more likely to be the problem :)
To find the DatasourceID's that are causing the problem, try running:

SELECT Datasource_ID, COUNT(Provider_Key)
FROM Provider
GROUP BY Datasource_ID
HAVING COUNT(Provider_Key) > 1

SELECT Datasource_ID, COUNT(Payor_Key)
FROM Payor
GROUP BY Datasource_ID
HAVING COUNT(Payor_Key) > 1

If either of the above 2 queries returns any rows, those
Datasource_ID's are the source of your problem. Check that your
unique keys are set up correctly, and that the UPDATE logic that you
are using is correct (maybe the tables store history and you need to
filter the Provider_Key/Payor_Key's by a date field?)

Good Luck!
J
 
 
 

The number of failing rows exceeds the maximum specified. Unspecified error

Post by Jay via SQ » Thu, 01 Nov 2007 04:27:30

Thanks, I will look into it. I just got another error that reads 'Error
Destination for the Row number 8779. Errors encountered so far in this task;
80. Unspecified error.' Not sure if this is connected to the same problem. It
is from the same DTS package. .



--
Message posted via SQLMonster.com
http://www.yqcomputer.com/