Strange Problem With TOP keyword

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)

Strange Problem With TOP keyword

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2010-01-05 : 04:19:25

hi,
i have a following query in a stored procedure:

select top 15 ID from Table1

when i am running above query in my development database(which is having Database Compatiability Level 80) it is working fine.

but when i ran this query in my production database(which is having Database Compatiability Level 65) it is giving error :
Incorrect Syntax Near Top.

we dont have rights to change production db compatiablity level.

how can i made the above query in my production database also.

top key word must be used in that query

any suggestions?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 04:30:49

select top(15) ID from Table1

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 04:32:15

Oh wait... You are using SQL Server 6.5?

TOP operator is not available then. You will have to use the "SET ROWCOUNT 15" syntax as this

SET ROWCOUNT 15
SELECT ID FROM Table1
SET ROWCOUNT 0

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 04:32:59

This is awkward because the SET ROWCOUNT syntax is deprecated and will cease to exist soon.

N 56°04'39.26"
E 12°55'05.63"

brian147
Starting Member

10 Posts

Posted - 2010-01-05 : 04:56:05

if you want to keep the same code for both, you might try:

declare @intCompLevel int
select @intCompLevel = cmptlevel from master.dbo.sysdatabases where name = DB_NAME();

if @intCompLevel < 70
begin
set rowcount 15
SELECT ID FROM Table1
set rowcount 0
end
else
begin
select top 15 ID FROM Table1
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources