Table alias "lei" not working

v2.5.14 - Query Analyzer

This works:
Select * From dbo.Date d where d.

This doesn't:
Select * From dbo.Date lei where lei.

lei is not setup as a snippet in T-SQL. Are there other aliases that won't work?

I am unable to reproduce this. Please try again, make sure you type a space after WHERE then type "leu." including the dot.

lei consistently regardless of database or table. Only Query Analyzer, not Mgmt Studio. Only when connected to one server. Closing and reopening QA still produces the same issue.

It sounds like you are connecting to wrong server and there is no such table or view or table function Date in the current database.

Does it work for table sysobjects? (SELECT * FROM sysobjects lei WHERE lei.)

When you type WHERE then press space, do you get a popup with the column names? If yes, what is displayed in the top most line? Does it say "lei -- alias for dbo.Date"?

Last edited by SysOp on Thu Aug 16, 2007 9:00 am; edited 1 time in total

I get the issue no matter what table name I select. When I press space after where, no column names appear. If I change the alias it works fine.

Are you sure there is no "lei" object or schema in the database?
Have you tried sysobjects?

	Code:
	SELECT * FROM sysobjects lei WHERE lei.

Found the possible connection: There is a user named lei, but no schema and no access to any databases. In fact if you alias a table by any standard SQL login user, you will see the issue.

I'm glad you found the cause. The lei user is still reported as a schema owner, it is a database user, not a login. When you type "lei." the database names are searched first, then schema names, then object names, then aliases. So the "lei" is found in schema. Because there are no objects in the schema, the prompt list is empty and the popup is not shown. This makes sense to me. In order to avoid getting into this situation again, please use aliases that don't match schema or database names.