BUG: Assignment to @variable Through SET Statement May Fail with Error Message 107 When You Use SELECT with Correlated Subquery (288232)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q288232

BUG #: 101262 (SQLBUG_70)

SYMPTOMS

A SET statement to set a local variable using a SELECT statement that contains a correlated subquery fails and this error message occurs:

Server: Msg 107, Level 16, State 2, Line 6
The column prefix 'I1' does not match with a table name or alias name used in the query.

WORKAROUND

Use a SELECT statement rather than a SET statement to assign a value to the variable.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

The following sample query provides an example of the type of query affected:

SET @Missing = (select count(*)
               from  IMAGE I1
               where share = 'A'
               and   extension = 'BMP'
               and   exists (select *
                             from  IMAGE I2
                             where I2.nbr = I1.nbr
                             and   I2.share = I1.share
                             and   I2.extension = I1.extension) )

Modification Type:	Major	Last Reviewed:	10/16/2002
Keywords:	kbBug kbDSupport KB288232