Post reply

Cross Apply error

  • March 19, 2015 at 11:50 am

    #398214

    Hi all, i hope some one can help me, i have this query but give me the error " Incorrect syntax near '.'. "

    and i don´t understand why.

    select *

    FROM dbo.Registos r

    CROSS APPLY dbo.fnObtemCategoriaAviacao(r.PaisOrigem, r.PaisDestino)

    thanks.

  • March 19, 2015 at 11:57 am

    #1785182

    Check SELECT @@VERSION

    It can be you're using some old SQL Server, alternatively, you might need to alias that function call ie:

    select *

    FROM dbo.Registos r

    CROSS APPLY dbo.fnObtemCategoriaAviacao(r.PaisOrigem, r.PaisDestino) x

    For me the code worked though, so perhaps you didn't send complete query?

  • March 19, 2015 at 12:03 pm

    #1785185

    thank you for the reply.

    my version is

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64) Sep 26 2012 17:08:07 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

  • March 19, 2015 at 12:31 pm

    #1785194

    Ok, what about this query?

    SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID()

    It's not that you have compatibility 80 (sql server 2000)?

  • March 20, 2015 at 4:18 am

    #1785299

    hello, i execute the script and the result was 80

  • March 20, 2015 at 4:26 am

    #1785300

    renato.mgomes (3/20/2015)

    hello, i execute the script and the result was 80

    Cross Apply didn't exist in SQL Server 2000 and it doesn't work if a database is set to compatibility mode 80. You will need to either change the compat mode to 100 (SQL 2008), or find a different way of writing the query that doesn't use APPLY

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2015 at 4:35 am

    #1785303

    ok, thanks.

    So i have to do the query in another way, because i can´t change the SQL configurations.

    thanks again.

  • March 20, 2015 at 2:05 pm

    #1785474

    I think you can do it by running from master and explicitly specifying the actual db name in the query:

    use master

    select *

    FROM your_db_name.dbo.Registos r

    CROSS APPLY your_db_name.dbo.fnObtemCategoriaAviacao(r.PaisOrigem, r.PaisDestino)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 8 posts - 1 through 7 (of 7 total)

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