If you have a database that’s been upgraded from SQL Server 6.5 to a newer version (like 7 or 2000), but it’s still in compatibility mode 65, you can’t use keywords that don’t exist in this compatibility mode. One example of such a keyword is TOP. Look at the following SQL statement:
SELECT TOP 10 * FROM My65Table ORDER BY 1
If this statement is executed in a database with compatibility mode 65, the following error message will be generated:
Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '10'.
To use TOP with a table in the upgraded 6.5 database, execute the command from another database in a higher compatibility mode, i.e. master:
USE masterGOSELECT TOP 10 * FROM .dbo.My65Table ORDER BY 1