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.
- 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'