If you’re getting SQL Server error 107 that goes something like “The column prefix ‘e’ does not match with a table name or alias name used in the query“, look at your column prefix.

This error occurs when we use the asterisk wildcard (*) that’s incorrectly qualified with a column prefix. The prefix, if provided, must match a table name or alias name. If you’re using a table alias, then you must use that instead of the table name.

Example of Error

Here’s an example of code that produces the error:

SELECT e.* FROM Employees;

Result:

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

Here, the error occurred because we’re referencing an alias that doesn’t exist. We’re referencing an alias called e, but we haven’t defined that in the FROM clause.

We can also get the same error by switching things around a bit:

SELECT Employees.* FROM Employees e;

Result:

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

This time we defined an alias, but we’re now getting the error because we’re not referencing that alias in our SELECT list. If we define an alias, then we must use it in our SELECT list instead of the table name.

The problem is basically has the same cause – there’s an issue between the prefix and the actual table name/alias.

Solution

Fortunately the solution is quite simple. We can fix the first example by defining the alias in the FROM clause:

SELECT e.* FROM Employees e;

Result:

EmployeeID  FirstName  LastName  Salary
----------  ---------  --------  ------
1           Hazy       Simper    80000 
2           Butch      Felding   65000 
3           Hamish     Parsley   70000 

All I did was append e to the query. In other words, I defined the alias that I was referencing in the SELECT clause.

And here’s another way to fix the problem:

SELECT Employees.* FROM Employees;

Result:

EmployeeID  FirstName  LastName  Salary
----------  ---------  --------  ------
1           Hazy       Simper    80000 
2           Butch      Felding   65000 
3           Hamish     Parsley   70000 

In this case I simply removed the alias altogether and referenced the table by its actual name.

We can also resolve this particular issue by removing the table name and the alias from the SELECT list altogether:

SELECT * FROM Employees;

Result:

EmployeeID  FirstName  LastName  Salary
----------  ---------  --------  ------
1           Hazy       Simper    80000 
2           Butch      Felding   65000 
3           Hamish     Parsley   70000 

Most of the time when we use aliases, our queries will be much more complex than this, but I thought it was worth mentioning in case it wasn’t already obvious.

It’s also worth mentioning that this error only occurs when we use the asterisk wildcard (*). If we explicitly name the columns, we’ll get a different error (Error Msg 4104), although the solution is basically the same.