SQL Server 2000 "Selection does not work" with TOP clause
Using SQL Server 2000 Developer Edition, why would this code:
select top 10 * from table
will result in an 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
will result in an error:
Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'Left'.
The answer was database compatible. It was set at 60 and was supposed to be 80.
Here's an excerpt 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 now everything is fine.
I don't see any reason why this might break, it might have to do with the installation or a compatibility issue with the installation. How do you fulfill requests? From the Query Analyzer?
As a few quick suggestions, try putting 10 in parentheses. SELECT TOP (10) * FROM ..... Also, try merging your tables using the AS keyword. e.g. SELECT * FROM master AS m ...
Both queries shouldn't work wrong there. Is it executed from the Query Analyzer?
I can reproduce the errors if I only select "SELECT TOP 10" from the first query and "SELECT * FROM MASTER M LEFT" for the second. So, if you are not running a query from Query Analyzer, maybe your query will be modified in the code in your application somewhere?
requests have no problem. which query parser are you using (since MSDE has no GUI)?