月度存档: 十一月 2010

ORDER BY items must appear in the select list …

I got an error message on one of the sql,

orDER BY items must appear in the select list if Select DISTINCT is specified.

this database was upgraded from mssql2000 to mssql2005, but this sql works well on the old database, and also it works well on other mssql2005 pt, dev. so there must be something wrong with the prod database or db server or mssql2005 sw configuration.

I copied PD database to pt server and restored it there, the sql returned error too. so I believe it's about database.
I suddenly recalled that I changed the compatibility level on new sql2005 from “SQL Server 2000(80)” to “SQL Server 2005 (90)”, and once I changed it back, the sql runs fine.

Here is the steps how to change the compatibility level.
rigt click the database name > properties > Options > Compatibility Level > change it to “SQL Server 2000 (80)”