SELECT TOP error

[domster]

Hi, I'm hoping someone can tell me why this statement isn't working:

SELECT TOP 1 * FROM [table name]

The error I get is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '1'.

If I check the syntax, it says it's OK, but when I try to execute it I get the error. Is this a problem with SQL Server 2000? Is there any way around it? Thanks in advance for any help.

Dom

 

[Bygbobbo]

i have run into this problem with non converted 6.5 SQL databases in a 2000 environment.

Check to make sure that the database type is sql 8.0.

You can try this though:

Run your query from a known 8.0 version database (using query analyzer) and point it towards your database that your trying to query with the TOP function.

e.g.
select top 1 *
from <server>.<database>.<owner>.

Bygs

 

[domster]

Thanks for the quick reply bygs. A naive question: how would I go about converting a database to 8.0? Thanks,

Dom

 

[SQLBill]

As Bygs pointed out the version is an issue when you are using a 6.5 or 7 database on a SQL Server 2000 server. Your database is already set to 8.0. Version 8.0 is SQL Server 2000.

What happens if you list the columns instead of using the *?

-SQLBill

 

[domster]

Same thing:

SELECT TOP 1 id,code FROM CALD_NETWORK

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '1'.

 

[SQLBill]

What application are you using to run the query?

-SQLBill

 

[domster]

I'm running it in ASP (hope that's what you meant) - the code is roughly as follows:

SQLStmt = &quot;SELECT TOP 1 id,code FROM &quot; & ProductId & &quot;_NETWORK&quot;
set page = Conn2.execute(SQLstmt)

However, if I try to query the table within Enterprise Manager, the syntax parses okay, but when I run the query I get the same error message (Incorrect syntax near '1'). Weird.

 

[SQLBill]

Put id in square brackets [] like this:

SQLStmt = &quot;SELECT TOP 1 [id],code FROM &quot; & ProductId & &quot;_NETWORK&quot;
set page = Conn2.execute(SQLstmt)

ID is a reserved word in SQL Server.

-SQLBill

 

[swampBoogie]

ID is a reserved word in SQL Server.

It's not. This has to do with the compatibility level.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_5c8c.asp

 

[domster]

Yup, it's a 6.5 database. How easy is it to upgrade? Or can you suggest another way of doing this - basically I've got a table of registration codes, when one is requested I want to get the first one in the table, delete it and give it to the user.

 

[swampBoogie]

set rowcount 1
select id,code FROM CALD_NETWORK

 

[domster]

Nice one, thanks - and thanks to everyone else who helped, too.

Dom

 

[SQLBill]

SwampBoogie,

You are right about ID not being a reserved word. I looked in BOL and sure enough it's not in the list. But when I create a query in QA it puts ID in blue the same as other Reserved Keywords....hmmmmmm

-SQLBill

 

[tlbroadbent]

If you are running SQL Server 2000, the database is a SQL 2000 databse. However, the compatibility level might be set to 65 which would prevent TOP from working. You can set the compatibility level for the database with the system stored procedure, sp_dbcmptlevel. It is explained in SQL Server Books Online.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm