TOP create syntax error

  • noeld

    SSC Guru

    Points: 96590

    may I ask what SP level is your server running?

    select serverproperty('ProductVersion'),serverproperty('ProductLevel')

     


    * Noel

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    That fails too... that's why we're all stumped :

    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

  • Jersey Moe

    SSChasing Mays

    Points: 652

    tried it with the order by ..still get the error.

  • Jersey Moe

    SSChasing Mays

    Points: 652

    Well that does work.  But, what I was tryig to do was something like this

    create my_view

    as

    select top 100 percent *

    from my_table

    order by column1

    This way view is pre sorted, since you can not have just an order by within a view.

    Jersey

  • Jersey Moe

    SSChasing Mays

    Points: 652

    8.00.194 RTM

     was the result

  • Jersey Moe

    SSChasing Mays

    Points: 652

    yep order by works just fine.

  • noeld

    SSC Guru

    Points: 96590

    RTM = Release To Manufacture.

    You have the original retail version of SQL server installed you need to at least go to SP3a or SP4 if you can!!!

    Cheers!

     


    * Noel

  • Jersey Moe

    SSChasing Mays

    Points: 652

    Hmmm I was under the impression that the service packs had been installed.

    Thanks for pointing that out.  I think the datacenter has some explaining to do!

  • noeld

    SSC Guru

    Points: 96590

    Sorry for me to be the bad news bearer

    But is is really important that you patch that server ASAP


    * Noel

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    I think that it's an understatement. Time to use the word needs to be done last year .

  • SQLBill

    SSC Guru

    Points: 51440

    Update your service pack to at least SP3a and make sure your SA account has a strong password.  Then retry your script.

    -SQLBill

  • Jersey Moe

    SSChasing Mays

    Points: 652

    No its a good catch.  I was under the impression that service pack 3a had been applied during a recent server upgrade project.  Turns out that because my then server admin was sacked by HR, he never gave the instructions to the new admin.

    Well that life in the corp world today. 

    Thanks for all the feed back. 

    --JerseyMo

     

  • David Burrows

    SSC Guru

    Points: 64575

    Not quite true. TOP keyword is SQL7 (level 70) onwards only and the error is a symptom of compatabilty albeit the poster says it is not.

    Remi, your code works because of the way sp_dbcmptlevel works

    sp_dbcmptlevel affects the behaviors in the specified database, not the entire server. The compatibility setting for a database takes effect when the database is made the current database with the USE statement, or if the database is the default database for the login. When a stored procedure is executed, the current compatibility level of the database in which the procedure is defined is used. All stored procedures in the database are recompiled when the compatibility setting is changed in that database.

    If you put batch separator (GO) after each exec sp_dbcmptlevel then you will see an error

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Wow, thanx for the tip. Here's the corrected version and results :

    create database test

    use test

    exec sp_dbcmptlevel 'Test', 80

    GO

    exec sp_dbcmptlevel 'Test'

    Select top 10 * from dbo.SysObjects

    --no error given

    exec sp_dbcmptlevel 'Test', 70

    GO

    exec sp_dbcmptlevel 'Test'

    Select top 10 * from dbo.SysObjects

    --no error given

    exec sp_dbcmptlevel 'Test', 65

    GO

    exec sp_dbcmptlevel 'Test'

    Select top 10 * from dbo.SysObjects

    --FAILS

    exec sp_dbcmptlevel 'Test', 60

    GO

    exec sp_dbcmptlevel 'Test'

    Select top 10 * from dbo.SysObjects

    --FAILS

    /*

    use master

    drop database test

    */

  • Jersey Moe

    SSChasing Mays

    Points: 652

    Thats it.  I adjusted the comp level and its working.

    Now this opens the disscusion of what are the potential gottcha's.

     

Viewing 15 posts - 16 through 30 (of 35 total)

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