|
|
Posted by Erland Sommarskog on 10/02/07 21:33
rdraider (rdraider@sbcglobal.net) writes:
> I have a strange issue and was hoping somebody could explain. The below
> query gives an ambiquous column name error on an install of SQL 2005
> Standard, sp2. This same query works fine on another install of SQL 2000
> Standard sp4 and SQL 2005 Workgroup. The order by clause is causing the
> error. I know this is easily fixed, just wondering why the error only
> occurs on a particular install of SQL 2005.
>
> SELECT DISTINCT ProcessName, ProcessName
> FROM ProcessLog
> ORDER BY ProcessName
You get an error because this is incorrect SQL. ORDER BY is special,
because this is the only place where you can refer to columns in the SELECT
list. But there are two ProcessName, which of them do you want to order by?
That may seem like a silly question but consider:
select top 20 a = CustomerID, a = OrderID
from Orders
order by a
In SQL 2000, this sorts by CustomerID which is obviously a bug. It should
give you the same error message as SQL 2005 does. The fact that is
accepted on SQL 2005 workgroup, I would assume is due to that you ran it
in a database with the compatibility level set set to 80 (= SQL 2000).
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|