Knowledge Base |

# FIX: False Error 512 on Subqueries with Version 4.21.006

Article ID: 124059

Article Last Modified on 3/24/2000

APPLIES TO

- Microsoft SQL Server 4.21a Standard Edition

This article was previously published under Q124059

## 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

## STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21.006. This problem was corrected in the latest U.S. Service Pack for SQL Server version 4.21a. For information on obtaining the Service Pack please contact your primary support provider.

Additional query words: subquery expression Windows NT

Keywords: kbbug kbfix KB124059