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)”

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>