SQL Server 2000 "Select is broken" with TOP clause

Question

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 'left'.

Answer 1

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 ...

Answer 2

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?

Answer 3

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.

Answer 4

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