TOP create syntax error

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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

     

  • andrewkane17

    SSCertifiable

    Points: 7469

    Do you have different collations on these databases? If so maybe its a case issue

  • Jersey Moe

    SSChasing Mays

    Points: 652

    No I checked that, and its the same

    SQL_Latin1_General_CP850_BIN

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    R u getting this error in a subquery/derived table or just like that?

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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.

  • SQLBill

    SSC Guru

    Points: 51440

    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

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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.

     

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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

    */

  • Jersey Moe

    SSChasing Mays

    Points: 652

    So, that seems to eliminate the level as being suspect.

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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).

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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.....

     

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Have you run the standard dbcc checks for the db?

  • SQLBill

    SSC Guru

    Points: 51440

    Try this:

    USE Finance

    SET ROWCOUNT 10

    SELECT *

    FROM SysObjects

    See if that works in both databases.

    -SQLBill

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Are there other objects that use the order by? Do they work?

  • SQLBill

    SSC Guru

    Points: 51440

    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 35 total)

You must be logged in to reply to this topic. Login to reply