why doesn't "Select TOP..." work? SQL Server V7

[Technokrat]

I'm running SQL Server V7 on two different servers, and the "select top..." statement works on one, but not the other. Any ideas?

Some specifics:
Server1:

select top 1 JSO.SO_Num from JOBS_SO JSO

SO_Num
-----------
1

(1 row(s) affected)
------------------------------------
Server2:

select top 1 JSO.SO_Num from JOBS_SO JSO

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

 

[tlbroadbent]

Check the compatibility level of the databases. TOP will not work in databases set to compatability level 6.5 or lower.

Use sp_dbcmptlevel to check the level.
exec sp_dbcmptlevel @dbname = dbname

Set the new level - use with caution because it may break other T-SQL code.
exec sp_dbcmptlevel @dbname = dbname, @new_cmptlevel = 70 Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin