Problem with TOP statement after SQL upgrade

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with TOP statement after SQL upgrade

Author

Topic

bc2m
Starting Member

1 Post

Posted - 2002-08-20 : 05:33:58

I recently upgraded SQL 6.5 to SQL 2000. We had some problems with
msdb which I managed to fixed but has anyone met this one:

One of the reasons for upgrading was to get access to the
extra SQL syntax, especially the TOP statement eg: "Select
TOP 5 * from tablex"

When I try to use this, either on the SQL Query Analyser or
in Delphi I get "syntax error '5' not recognised". Is this
a consequence of upgrading, and is SQL 2000 still in SQL 6.5
mode? Is there a way to enable this, or am I going to have
to reinstall SQL 2000? I don't have this problem on a
"clean" SQL 2000 install.

Any help would be much appreciated...

TIA

Barry

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-20 : 07:19:20

Your database is probably in 6.5 compatability mode

run this in QA

sp_dbcmptlevel 'databasename'

It will probably be 65. It needs to be 70 or 80 for TOP to work.
You can modify this using the same procedure

sp_dbcmptlevel 'databasename',80

HOWEVER - this will have implications more than enabling the TOP operator so make sure you test everything and read up on the changes between 6.5 and 2000 to make sure you appreciate the consequences. As always try this in a test environment first.

HTH
Jasper Smith

Edited by - jasper_smith on 08/20/2002 07:19:37

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-20 : 19:48:01

This is one of the reasons that it is generally recommended that you NOT upgrade the system databases (master, msdb, model, etc.) but rather stick with the new versions shipped in the new SQL version and only migrate data or scripts that are needed.

If you're not "in production" with this yet, you might consider going back to the beginning following this advice. If you are in production, I hope you have a test system that has the same issues so you can really test out all the effects of jasper's advice.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-08-21 : 11:30:31

quote:
HOWEVER - this will have implications more than enabling the TOP operator so make sure you test everything and read up on the changes between 6.5 and 2000 to make sure you appreciate the consequences. As always try this in a test environment first.

I want to re-emphasize this point, because CODE WORKS DIFFERENTLY when you upgrade your compatability level. BOL should have a good description of the changes involved with the different levels.

*************************
Someone done told you wrong!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources