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 only use the alias 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

See Also