512 error in delete statement

[codedhacker]

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?

[ck9663]

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?

That error happens when you're running a query that is expected to return a single result but returned two or more. It's usually when you use the result of a query and store in a variable or use it in an expression.One of the usual reason is there is a duplicate on the table. Since your DELETE query does not contain any subquery, I can only assume your error is somewhere else on your stored proc and not on your DAO.

-- CK