Error 107
Severity Level 15
Message Text
The column prefix '%.*ls' does not match with a table name or alias name used in the query.
Explanation
A column prefix was specified that does not correspond to any table name specified in the query. Match the column prefixes against the table names and alias names in the FROM clause.
One common cause of this error is the use of a table name when an alias name for the table is also supplied. When working with a table alias (a correlation name in ANSI terminology), the syntax checking in Microsoft® SQL Server™ complies with the ANSI specification. ANSI states,
A <table name> ... is exposed ... if and only if the <table reference>
does not specify a <correlation name>.
If an alias has been provided for a table name in the FROM clause, you can use the alias only to qualify columns from the table; the table name cannot be used elsewhere in the statement because they are flagged as syntax errors.
As an example of the difference in behavior, assume this script has been executed:
USE Northwind
GO
SELECT Customers.ContactName
FROM Customers cu
WHERE ContactName LIKE 'C%'
GO
SELECT cu.ContactName
FROM Customers cu
WHERE Customers.ContactName LIKE 'C%'
GO
In both SELECT statements, notice the use of Customers to qualify the column ContactName even though a table alias of cu has been provided to substitute for the table name. Both of these queries return this error message:
Server: Msg 107, Level 16, State 3
The column prefix 'Customers' does not match with a table name or alias name used in the query.
Action
Use the column prefix that corresponds to the exposed name of the table.
Rewrite any queries where column names are qualified with the table name. Use the table alias instead. For example, this SELECT statement is equivalent to the ones above and uses a table alias for column qualification:
USE Northwind
GO
SELECT cu.ContactName
FROM Customers cu
WHERE cu.ContactName LIKE 'C%'
GO