FIX: False Error 512 on Subqueries with Version 4.21.006

ID: Q124059

4.21.006 WINDOWS kbbug4.21.006 kbprg kbfix4.21a.sp1

The information in this article applies to:

Microsoft SQL Server, version 4.21.006

BUG# NT: 929 (4.21.006)

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 reference words: 4.21.006 subquery expression Windows NT KBCategory: kbprg KBSubcategory: SSrvWinNT

Last Reviewed: December 19, 1997