SQL Server 2000 "Select is broken" with TOP clause

Using SQL Server 2000 Developer Edition, why would this code:

select top 10 * from table

result in this error:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '10'.

and this code:

select * from master m
left join locality l on m.localityid = l.localityid

result in this error:

Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'l开发者_如何学Pythoneft'.

The answer was database compatibility. It was set to 60, and should have been 80.

Here's an extract from http://msdn.microsoft.com/en-us/library/bb510680.aspx

The value must be one of the following:
- 80 = SQL Server 2000
- 90 = SQL Server 2005
- 100 = SQL Server 2008

I changed it to 80 and all is well, now.

I see no reason why this would break, it might be due to a setup or compatibility issue with the installation. How are you running the queries? From Query Analyser?

As a couple of quick suggestions though, try putting the 10 in brackets. SELECT TOP (10) * FROM ..... Also, try aliasing your tables using the AS keyword. e.g. SELECT * FROM master AS m ...

Both queries should work nothing wrong there. Are you executing from the Query Analyzer?

I can reproduce the errors if i select only "SELECT TOP 10" from the first query and "SELECT * FROM MASTER M LEFT" for the second. So if you aren't executing from the Query Analyzer, maybe your querystring gets altered in code in you application somewhere?

the queries have no problem. which query analyzer are you using (as MSDE has no GUI)?