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?

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 (AT) yahoo (DOT) com> wrote

(phdscholar80 (AT) yahoo (DOT) com) writes:
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.

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

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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