TOP create syntax error
-
Has anyone come across this when using TOP?
It works in one database but not another.
use finance
select TOP 10 *
from sysobjects
Results:Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '10'.
use pubs
select TOP 10 *
from sysobjects
Results:
sysobjects 1 S 1 25 -536870909 96 0 0 2000-08-06 01:29:12.717 0 96 0 S 1 113 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
sysindexes 2 S 1 29 -536870907 32 0 0 2000-08-06 01:29:12.717 0 32 0 S 1 8273 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
syscolumns 3 S 1 32 -536870909 80 0 0 2000-08-06 01:29:12.717 0 80 0 S 1 113 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
systypes 4 S 1 20 -536870909 80 0 0 2000-08-06 01:29:12.717 0 80 0 S 1 113 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
syscomments 6 S 1 10 -536870911 48 0 0 2000-08-06 01:29:12.717 0 48 0 S 1 81 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
sysfiles1 8 S 1 4 -536870912 0 0 0 2000-08-06 01:29:12.717 0 0 0 S 1 65 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
syspermissions 9 S 1 11 -536870911 16 0 0 2000-08-06 01:29:12.717 0 16 0 S 1 81 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
sysusers 10 S 1 20 -536870909 96 0 0 2000-08-06 01:29:12.717 0 96 0 S 1 113 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
sysproperties 11 S 1 5 -536870911 32 0 0 2000-08-06 01:29:12.717 0 32 0 S 1 81 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
sysdepends 12 S 1 11 -536870909 64 0 0 2000-08-06 01:29:12.717 0 64 0 S 1 113 0 2000-08-06 01:29:12.717 0 0 0 0 0 2 0
-
Do you have different collations on these databases? If so maybe its a case issue
-
No I checked that, and its the same
SQL_Latin1_General_CP850_BIN
-
R u getting this error in a subquery/derived table or just like that?
-
No just like that. I first tried to add it toa view so that I could put in an ORDER BY. Then just tried a general select statement. As the one I posted.
-
Check what Compatibility Level the database is set for.
Use Enterprise Manager, find the database, right click, select Properties, go to OPTIONS tab. At the bottom it will show your Compatibility Level. It should be 80.
-SQLBill
-
That was the first thing checked but, not the cause of the issue.
I have my data center guys scrating thier heads over this one.
-
I had thaugh of this one too. But it worked right down to 60 so I didn't bother to ask him
.create database test
use test
exec sp_dbcmptlevel 'Test', 80
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
exec sp_dbcmptlevel 'Test', 70
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
exec sp_dbcmptlevel 'Test', 65
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
exec sp_dbcmptlevel 'Test', 60
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
--no error given
/*
use master
drop database test
*/
-
So, that seems to eliminate the level as being suspect.
-
Yup... this one is really hard to catch...
Have you tried copying the db to another server and see if it does the same thing there (to expose the server as the problem).
-
Interesting enough I just refreshed my test server from a backup from production yesterday. And it fails on both servers.
Mind you, the only reason I even came across this was to try and add an ORDER BY to a view.
Guess that's what I get for trying to be nice to the end users.....
-
Have you run the standard dbcc checks for the db?
-
Try this:
USE Finance
SET ROWCOUNT 10
SELECT *
FROM SysObjects
See if that works in both databases.
-SQLBill
-
Are there other objects that use the order by? Do they work?
-
Did you say you are using this for a view? Try adding an ORDER BY to the script and see if that works.
-SQLBill
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply