TOP n not working for some tables SQL Server 2000

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)

TOP n not working for some tables SQL Server 2000

Author

Topic

jgolley
Starting Member

4 Posts

Posted - 2009-04-10 : 19:07:23

I am running an older version of SQL Server (2000) and I use TOP n all the time without any problem. I recently started on a new project and restored a backup of my Client's database (they had the same version of SQL Server), but the TOP n doesn't seem to work.

Running a simple script on my tables works, but on the client's tables the TOP argument doesn't work. I'm testing the statements in SQL Query Analyzer

SELECT TOP 50 * FROM tblMailFile (does not work for their database)

SELECT TOP 50 * FROM contacts (works fine for my database)

Why would it not work for some Tables/Databases?

Thanks for any help.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-11 : 05:27:33

What do you mean by 'does not work'?
More than 50 rows?
Less that 50 rows?
Error? (if so, what error)

In future please post in the correct forum. The Article Discussion forum is for discussions on published articles, not general T-SQL questions

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-11 : 06:54:16

If you think the records should be sorted, think again.
If you want specific TOP 50 PERCENT, you MUST use ORDER BY.

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

jgolley
Starting Member

4 Posts

Posted - 2009-04-11 : 09:25:55

I apologize for not being in the correct forum, I am new here as I am extremely frustrated. I also apologize for not being clear. I get an error as if it is not supported:

select top 50 * from tblmailfile

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

The table has a million rows or so.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-11 : 09:55:55

Try
select top (50) * from tblmailfile

or tell us what tool you are using to execute the query.

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

jgolley
Starting Member

4 Posts

Posted - 2009-04-11 : 12:19:38

select top(50) * from tblmailfile

Generates this error: Server: Msg 195, Level 15, State 10, Line 1
'top' is not a recognized function name.

I am using SQL Query Analyzer from the SQL Server Enterprise Manager

The syntx SELECT TOP 50 * FROM contacts works fine when I'm working on another database within the same SQL Enterprise Management and SQL Query Analyzer. I change the database identified in the drop down at the top and execute the same syntax and it fails. Strange, huh?

jgolley
Starting Member

4 Posts

Posted - 2009-04-11 : 12:28:57

I found my solution...

in Enterprise Manager
Right-Click on the database
click properties
click Options.

compatibility level should be 70 or 80.

That database's compatibility was at 65. When I changed it to 70, it worked. Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources