sql - "Ambiguous column name" error on one particular server

Question

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.

Answer 1

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