TOP Syntax Error - 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

Transact-SQL (2000)

TOP Syntax Error

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2005-01-27 : 12:02:34

I keep getting a syntax error when attempting to use the TOP keyword in a SELECT statement. Is this due to the database being set to a compatability level of 65?

If this is the case, is my only other option to use ROWCOUNT?

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-27 : 13:04:12

It would be kinda helpful to actually see the statement, and what the actual error that's being produced...

Brett

8-)

BCrowe
Starting Member

23 Posts

Posted - 2005-01-27 : 13:29:20

I assure you it's not the syntax as I can run similar queries on a different database but for the sake of clarity...

SELECT TOP 1 VendName
FROM tpoPOLine
INNER JOIN tpoPurchOrder
	ON tpoPOLine.POKey = tpoPurchOrder.POKey
INNER JOIN tapVendor
	ON tpoPurchOrder.VendKey = tapVendor.VendKey
WHERE tpoPOLine.ItemKey = @ItemKey
ORDER BY tpoPurchOrder.TranDate DESC

Error:

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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 14:07:28

quote:
Originally posted by BCrowe

I assure you it's not the syntax as I can run similar queries on a different database but for the sake of clarity...

SELECT TOP 1 VendName
FROM tpoPOLine
INNER JOIN tpoPurchOrder
ON tpoPOLine.POKey = tpoPurchOrder.POKey
INNER JOIN tapVendor
ON tpoPurchOrder.VendKey = tapVendor.VendKey
WHERE tpoPOLine.ItemKey = @ItemKey
ORDER BY tpoPurchOrder.TranDate DESC

Error:

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

Perhaps the file itself is no longer EXTANT? Or on a different table?

I get the same when I tested trying to select top 1 from a file that no longer existed!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 14:17:34

BCrowe,

Ooops....I gave you bum scoop

when I tried to generate the same error you got the result was

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MyFile'.

...which is NOT the error you're trying to resolve. I'll keep looking at this. Sorry 'bout the false hope.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-01-27 : 14:41:33

quote:

Is this due to the database being set to a compatability level of 65?

Yes.

quote:

If this is the case, is my only other option to use ROWCOUNT?

Yes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources