TOP command won't work - SQL Server Forums

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

SQL Server Development (2000)

TOP command won't work

Author

Topic

aawan
Starting Member

24 Posts

Posted - 2005-07-13 : 17:47:16

I use the top command a lot in most of my databases if I want to limit the number of rows returned by a query.

Now the top command works in every database on my server except one.
I have posted the error that I get below, along with the SQL.

First I thought maybe it had something to do with the ROWCOUNT being accidentally set. But I did a "SET ROWCOUNT 0" and I still get that error.

Any help is greatly appreciated.

Thanks!
Ali

The sql is
Select TOP 1 *
FROM TableName

The error that I get back is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 17:50:21

Right click on your database in Enterprise Manager. Go to the Options tab. What is the compatibility level set to?

Tara

aawan
Starting Member

24 Posts

Posted - 2005-07-13 : 17:51:40

Tara,

The compatibility level is set to 65.

Thanks,
Ali

quote:
Originally posted by tduggan

Right click on your database in Enterprise Manager. Go to the Options tab. What is the compatibility level set to?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 17:54:01

TOP isn't supported in 65 compatibility level. It is only supported in 70 or 80. Your database must have been upgraded from SQL Server 6.5 to 2000 and the person doing the upgrade either forgot to change the compatibility level or they wanted to stay at 65 for various reasons. I do not recommend changing this option unless you know your application can support the higher compatibility level. Do not do this in production unless it has been thoroughly tested in a test environment.

Tara

aawan
Starting Member

24 Posts

Posted - 2005-07-13 : 17:58:03

Thanks a bunch Tara!
This really helps and at least points me in the right direction.
Don't worry I don't plan on making a database-wide change in production.

quote:
Originally posted by tduggan

TOP isn't supported in 65 compatibility level. It is only supported in 70 or 80. Your database must have been upgraded from SQL Server 6.5 to 2000 and the person doing the upgrade either forgot to change the compatibility level or they wanted to stay at 65 for various reasons. I do not recommend changing this option unless you know your application can support the higher compatibility level. Do not do this in production unless it has been thoroughly tested in a test environment.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources