Posted by rdraider on 10/02/07 22:04

You are correct about the compatibility level on SQL 2005 set to 80.
I know this is incorrect SQL but it comes from a web app. I went ahead and
edited the ASP page but will need to have the developer fix it.

Thanks.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99BDF09021E94Yazorman@127.0.0.1...
> 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

[Back to original message]