Order by [alias] in 2005 issue

So this 'order by' works in SQL2000, but will error in 2005 -

select top 1 a.forderID [orderId] from MyTable a
order by a.orderid

SQL2005 eRR:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'orderid'.

How to work around? Is there a server setting that will act like SQL2000? We
have literally thousands of sprocs, and in-line sql all over... gonna be a
challenge.

Any help much appreciated.

Thanks,
Chris

Chris wrote:
> So this 'order by' works in SQL2000, but will error in 2005 -
>
> select top 1 a.forderID [orderId] from MyTable a
> order by a.orderid
>
> SQL2005 eRR:
> Msg 207, Level 16, State 1, Line 2
> Invalid column name 'orderid'.
>
> How to work around? Is there a server setting that will act like SQL2000? We
> have literally thousands of sprocs, and in-line sql all over... gonna be a
> challenge.
>
> Any help much appreciated.
>
> Thanks,
> Chris

This is documented behavior, and the SQL 2005 Upgrade Advisor would have
warned you had you run it.
http://msdn2.microsoft.com/en-us/library/ms143359.aspx



--
Tracy McKibben
MCDBA
http://www.realsqlguy.com