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'.
source to share
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.
source to share
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 ...
source to share
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?
source to share