On Oct 29, 3:45 pm, "jay via SQLMonster.com" <u7124@uwe> wrote:
> 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 viahttp://www.sqlmonster.com

>From the error message, it looks like you're using a sub-query that is

On Oct 29, 4:38 pm, "jay via SQLMonster.com" <u7124@uwe> wrote:
> 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
>
>
>
>
>
> jhofm...@googlemail.com wrote:
> >> I received the following error on a failed DTS package 'The number of failing
> >> rows exceeds the maximum specified. Unspecified error'.
> >[quoted text clipped - 17 lines]
> >> --
> >> Message posted viahttp://www.sqlmonster.com
>
> >Hi Jay,
>
> >>From the error message, it looks like you're using a sub-query that is
> >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
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200710/1- Hide quoted text -
>
> - Show quoted text -

jhofmeyr@googlemail.com wrote:
>> I received the following error on a failed DTS package 'The number of failing
>> rows exceeds the maximum specified. Unspecified error'.
>[quoted text clipped - 17 lines]
>> --
>> Message posted viahttp://www.sqlmonster.com
>
>Hi Jay,
>
>>From the error message, it looks like you're using a sub-query that is
>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

jhofmeyr@googlemail.com wrote:
>> J,
>>
>[quoted text clipped - 50 lines]
>>
>> - Show quoted text -
>
>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

On Oct 29, 7:09 pm, "jpettigrew via SQLMonster.com" <u7124@uwe> wrote:
> 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.
>
> jhofm...@googlemail.com wrote:
> >> J,
>
> >[quoted text clipped - 50 lines]
>
> >> - Show quoted text -
>
> >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
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200710/1

jhofmeyr@googlemail.com wrote:
>> Sorry, I am still new to this.
>>
>[quoted text clipped - 26 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200710/1
>
>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