I have a piece of code that uses the db-library with sql server
2000/2005 and runs the following delete statement:



DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some
Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5
IN( 'Some Val3' )



on TABLE1, uploads data into TABLE1 through bulk loading, calls a
stored procedure that uses the data, and then deletes the data through
the SAME delete statement with EXACTLY the same parameter values. The
first delete statement is always successful, but the second statement
intermittently gives the following error:



0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.

Note: I was initially using the equality operator instead of the IN
operator in the query but that gave the same results.

The table has the schema

Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL,
Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7
varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL,
Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL,
Col14 float NULL, Col15 float NULL )

Can somebody tell me whats going wrong here? I can easily ignore this
error because my work is done after the stored proc but I fear
amassing a lot of useless data in the table over time. Also
http://support.microsoft.com/kb/195491 talks about a case where the
delete statement is actually successful but still causes an error when
using ADO. I vaguely remember hearing somewhere that delete causes a
lot of problems if the table doesn't have primary keys. Is this
correct?

> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.

Since your DELETE statement has no subqueries, my guess is that you have a
DELETE trigger on the table that is causing this error. In that case,
examine and correct the trigger code.


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<phdscholar80@yahoo.com> wrote in message
news:abf71715-4fdc-4ad1-bff1-5b504d5b124f@i29g2000prf.googlegroups.com...
>I have a piece of code that uses the db-library with sql server
> 2000/2005 and runs the following delete statement:
>
>
>
> DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some
> Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5
> IN( 'Some Val3' )
>
>
>
> on TABLE1, uploads data into TABLE1 through bulk loading, calls a
> stored procedure that uses the data, and then deletes the data through
> the SAME delete statement with EXACTLY the same parameter values. The
> first delete statement is always successful, but the second statement
> intermittently gives the following error:
>
>
>
> 0,0,MS SQL Server Message :
> SQL Server message 512, state 1, severity 16:
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> SQL Server message 3621, state 0, severity 0:
> The statement has been terminated.
>
> Note: I was initially using the equality operator instead of the IN
> operator in the query but that gave the same results.
>
> The table has the schema
>
> Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL,
> Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7
> varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL,
> Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL,
> Col14 float NULL, Col15 float NULL )
>
> Can somebody tell me whats going wrong here? I can easily ignore this
> error because my work is done after the stored proc but I fear
> amassing a lot of useless data in the table over time. Also
> http://support.microsoft.com/kb/195491 talks about a case where the
> delete statement is actually successful but still causes an error when
> using ADO. I vaguely remember hearing somewhere that delete causes a
> lot of problems if the table doesn't have primary keys. Is this
> correct?

(phdscholar80@yahoo.com) writes:
> I have a piece of code that uses the db-library with sql server
> 2000/2005 and runs the following delete statement:

Beware that DB-Library is deprecated, and the version after SQL 2008
will not permit connections for DB-Library. Also, with DB-Library
you don't access to a lot of the new functionality added in SQL 7 and
later.

> on TABLE1, uploads data into TABLE1 through bulk loading, calls a
> stored procedure that uses the data, and then deletes the data through
> the SAME delete statement with EXACTLY the same parameter values. The
> first delete statement is always successful, but the second statement
> intermittently gives the following error:
>
>
>
> 0,0,MS SQL Server Message :
> SQL Server message 512, state 1, severity 16:
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.

As Dan said, the cause is likely to be found in a poorly written trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx