BUG: Cannot Refer to Column in a Query Using the Name of a Table Variable (296642)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

This article was previously published under Q296642

BUG #: 353650 (SHILOH_BUGS)

SYMPTOMS

If you try to refer to a column in a query using the name of a table variable, such as @table1.columnname, an error message similar to the following may be returned:

Server: Msg 137, Level 15, State 2, Line 8
Must declare the variable '@table1'.

WORKAROUND

To work around this problem, use an alias for the table variable:

select aulname, aufname
from @pubs p
join authors
  on (p.aulname = authors.au_lname and
      p.aufname = authors.au_fname)

STATUS

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

MORE INFORMATION

To reproduce this problem, run the following script:

use pubs
go

declare @pubs table (aulname varchar (40), aufname varchar (20))

insert into @pubs select au_lname, au_fname from authors

select aulname, aufname
from @pubs
join authors
  on (@pubs.aulname = authors.au_lname and
      @pubs.aufname = authors.au_fname)

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbbug kbpending KB296642