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 synt

Solution 1:

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.

Solution 2:

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

Solution 3:

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?

Solution 4:

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