SYMPTOMS
Some subqueries in a query select list that are correctly returning one row
may falsely return a 512 error in Server versions 4.21.006 or later.
A subquery in a select list must operate as an expression, which means it
is not legal for such queries to return more than one row. Prior to version
4.21.006, SQL Server would not detect this condition as an error and would
instead return the last row of the subquery result set.
This was filed as bug 550, and a bugfix was supplied in 4.21.006. However,
starting with this version, some queries that are correctly returning one
row are incorrectly generating the 512 error:
Subquery returned more than 1 value. This is illegal when
the subquery follows =, !=, <, <=, >, >=, or when the subquery is
used as an expression.
For example, if there are two tables:
Emp
empid name
1 Jane Doe
Prod
plantid process empid
1 1 1
1 2 1
The following query generates a false 512 error:
select (select name
from emp
where emp.empid = prod.empid)
from prod
WORKAROUND
If possible, recode the query as a join:
select name
from emp, prod
where emp.empid = prod.empid