SQL 7 Top n produces Error 1

[SimonTheSponge]

I have inherrited a SQL 7 Database. Although it appears not to support the Syntax Select top n from table. this always results in "incorrect syntax near 10". The database was upgraded apparently from 6.5. @@version displays Microsoft SQL server 7.0 - 7.00.1063.

It was my understaing that Top n was introduced with version 7 or was it introduced with a service pack

Cheers

 

[JamesLean]

It sounds like your database is still running at the 6.5 compatibility level. Use the following to check the current level:

EXEC sp_dbcmptlevel 'mydb'

If it is not 70 then you can change it using:

EXEC sp_dbcmptlevel 'mydb', 70

NB: I would test this before doing it on a live db.

--James

 

[SimonTheSponge]

Thanks James - spot on it's running 6.5 compaitibility level.

Incidentally when I try to add a trigger that references the updated, inserted or deleted cursors, it doesn't recognise them say that it can't find table UPDATED, INSERTED or DELETED. Would this be related at all (having never used 6.5, I have no idea how triggers deal with the current values in a trigger ;-))

 

[JamesLean]

As far as I'm aware you could create triggers that referenced the inserted and deleted pseudo tables in 6.5. There is no updated table in any version.

--James

 

[SimonTheSponge]

Ooops Sorry, got carried away there James with the UPDATED keyword ;-)