Sunday, May 29, 2022

The column prefix does not match with a table name or alias name used in the query

Msg 107, Level 16, State 3, Line 1
The column prefix does not match with a table name or alias name used in the query.

The actual Error message varies with the Alias being used. The usual message is as shown below.

The column prefix '%.*ls' does not match with a table name or alias name used in the query.

The % in the above can be replaced with the alias that was used and caused the issue.

For example, below are 2 scenarios that will lead to the SQL Error msg 107.

  • When the Column Prefix doesnot correspond to the table or alias name that is used in the SQL query. The below query leads to the error. 
Use AdventureWorks2017
Go

SELECT P.*
FROM Person.Person
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7
The column prefix 'P' does not match with a table name or alias name used in the query.

In this query, the alias P cannot be identified and will result
above error
  • The table name is specified as a column prefix where as the alias name is supplied in the FROM clause. 

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person As PR
where FirstName='Adam'
Output:

Msg 107, Level 15, State 1, Line 7
The column prefix 'Person' does not match with a table name or alias name used in the query.

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person
where FirstName='Adam'

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

Use AdventureWorks2017
Go

SELECT PR.*
FROM Person.Person As PR
where FirstName='Adam'

Too many table names in the query. The maximum allowable is %d

 While using SQL Server 2005, You might have exceeded maximum allowed tables.

This time the error message was Too many table names in the query. The maximum allowable is 256.

Unfortunately, it’s not very feasible to reduce the number of tables being used as that number is dependent on the query.

Higher version of sql server is not having such problem.