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

Error Message:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix ‘t1′ does not match with a table name or alias name used in the query.

Severity level:
16.

Description:
SQL Server allows to specify a table alias for each table involved in your query. Typically this is in the form

SELECT < list >
  FROM table AS alias_name_goes_here

However, SQL Server must able be able to unambiguously resolve column names. If you use use a column prefix for which SQL Server cannot find either a table or a table alias, this error is raised.     

Consequences:
The SQL statement cannot be parsed and further execution is stopped.

Resolution:
Error of the severity level 16 are generated by the user and can be fixed by the SQL Server user.

Versions:
All versions of SQL Server.

Example(s):
SELECT t1.OrderID
  FROM Northwind..Orders

Remarks:
As long as SQL Server can resolve the column name in a query, it is valid to write a statement such as:

SELECT OrderID
  FROM Northwind..Orders t1

Even though we have given the Northwind..Orders table the alias t1, we don’t use it in the SELECT list and still get a resultset returned. But what works in such simple scenarios is likely to break in complex queries. So, as a matter of fact, if you decide to use a table alias in a query, apply that alias consequently to all column of that table you reference in that particular statement. Not only do you reduce the likelihood of errors in code, but also you greatly improve its readability and maintenability.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |