This simple query throws the "Ambiguous column name TaskID" error on one db-server only. This is ridiculous. We tested this with the same database structure on different servers and different versions of SQL Server (2005 / 2008), and it's only THIS particular client's server that throws the error. I'm actually frustrated.
SELECT Tasks.TaskID
FROM Tasks
INNER JOIN TaskHelpers ON TaskHelpers.TaskID = Tasks.TaskID
ORDER BY TaskID
Yes, I know I can put Tasks.TaskID
into the order by
clause, but for some reasons I can't.
This means two columns have the same column name — that is the “Name” column. The SQL Machine is confused as to which “Name” out of the two tables you are referring to. It is ambiguous — not clear. To clarify this, add the alias of either or both TABLE1 or TABLE2 to the columns having the same name.
You may see an error that says something like Column 'id' in field list is ambiguous . This error means that there is a field name that is present in more than one table, so it needs to be scoped with the table name to avoid ambiguity: using orders.id instead of just id will resolve the issue.
An invalid column name error in SQL means that the column name violates the conditions of the column name. If you reference an object that does not exist in the table or the name exists, but you did not reference it correctly, you will get this error.
You select the table with ALTER TABLE table_name and then write which column to rename and what to rename it to with RENAME COLUMN old_name TO new_name .
You will get the ambiguous column name error if you run your query on sql server 2000, or under compatibility level 80 or less. On sql server 2005/2008 with compatibility level 90 or better, yur query runs fine.
From the order by clause docs:
"In SQL Server 2005, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement."
My Momma said always qualify EVERY column in a query with a table name/alias just like "always include all column names in INSERTs" and justs like "don't SELECT *", etc.
Other than making it easier because it is self documenting the source code, you prevent this error if you ever add/change columns.
check your compatibility levels, there are differences between them and the how ORDER BY works!
In general, in compatibility level 90 and higher, the default level for SQL Server 2008, an ORDER BY without a table name/alias statement produces the error.
ALTER DATABASE Compatibility Level (Transact-SQL) see section: Differences Between Compatibility Level 80 and Level 90
Compatibility-level setting of 80
WHEN binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.
For example, an ORDER BY clause with a single two-part column (.) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.
SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.
Compatibility-level setting of 90
Errors are raised on column ambiguities. Column prefixes, if any, specified in ORDER BY are not ignored when binding to a column defined in the SELECT list.
Consider the following query.
SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
When executed, the column prefix in the ORDER BY clause is not ignored. The sort operation occurs on the specified source column (x.c1) as expected. The execution plan for this query shows that the sort operator orders the rows returned from t_table and then the values for the derived column c1 defined in the SELECT list are computed.
You can specify the index of the column to sort instead:
SELECT Tasks.TaskID
FROM Tasks
INNER JOIN TaskHelpers ON TaskHelpers.TaskID = Tasks.TaskID
order by 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With