Wednesday, August 19, 2009

SQL Server Error Messages - Msg 107 - The column prefix does not match with a table name or alias name used in the query.prefix,table name,query,from

When running some Select statements theire is chance to come the following the error.

Server: 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 reason for this error is using table name as prefix of a column name and that table name is not included in the from clause. Anothe reason is you may be assigning an alias to the table name and prefix is still the table name.

I can explain the error with the help of following example

SELECT [Employees].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]


In the above cause i have used prefixed as customers and employeed bt in from clause theire is only customers.

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.

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]


To illustrate the second cause of this error, the following SELECT statement will generate the error:

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers] Cust


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:

SELECT [Cust].[FirstName], [Cust].[LastName]
FROM [dbo].[Customers] Cust

7 comments: