locked
Incorrect syntax near '+'. RRS feed

  • Question

  • Hi,

    how to resolve the problem with openquery?

    Msg 102, Level 15, State 1, Procedure p_da_insert_to_mlr_incl_admin, Line 78
    Incorrect syntax near '+'.


    Many Thanks & Best Regards, HuaMin Chen

    Friday, October 14, 2011 3:45 AM

Answers

  • Hi,

    I think openquery doesnt accept variables.

    Hope this helps.

    ~ J

    • Marked as answer by Jackson_1990 Friday, October 14, 2011 5:08 AM
    Friday, October 14, 2011 4:30 AM
  • OpenQuery does not accept variables, so, as already suggested, you need to create the whole thing as one string and then execute it.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Jackson_1990 Friday, October 14, 2011 5:08 AM
    Friday, October 14, 2011 5:00 AM

All replies

  • Are you using dynamic sql ??

    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, October 14, 2011 4:00 AM
  • Yes, by openquery
    Many Thanks & Best Regards, HuaMin Chen
    Friday, October 14, 2011 4:03 AM
  • The Script You Posted look incomplete. Please post Complete Script


    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Friday, October 14, 2011 4:05 AM
  • It is already the main part of the whole script. the bold line above is where the error happens. Thanks
    Many Thanks & Best Regards, HuaMin Chen
    Friday, October 14, 2011 4:13 AM
  • Hi,

    I think openquery doesnt accept variables.

    Hope this helps.

    ~ J

    • Marked as answer by Jackson_1990 Friday, October 14, 2011 5:08 AM
    Friday, October 14, 2011 4:30 AM
  • It seems to be not so good!


    Many Thanks & Best Regards, HuaMin Chen
    Friday, October 14, 2011 4:37 AM
  • Hi,

    Try putting your Openquery SQL into a variable and use sp_executesql

    e.g.

    DECLARE @SQL NVARCHAR(1000)
    SET @SQL = N'
    ....
       from openquery(ORA_IQSHIP,''select vp.vessel, ... '
    
    EXEC sp_executesql @SQL
    

     


    I assume you know which section of the above code you should replace. ;)

    Hope this helps.

    ~ J.

     

     


    Friday, October 14, 2011 4:55 AM
  • OpenQuery does not accept variables, so, as already suggested, you need to create the whole thing as one string and then execute it.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Jackson_1990 Friday, October 14, 2011 5:08 AM
    Friday, October 14, 2011 5:00 AM
  • Try Following

     Exec ('Select * from openquery(ORA_IQSHIP,''select vp.vessel,
       vp.voyage,
       vp.leg,
       PORT_SEQUENCE,
       PORT_OF_CALL,
       pt.port_name,
       to_char(nvl(ARRIVAL_DATE,''01-01-1900''),''dd-mm-yyyy'') arrival_date,
       to_char(nvl(nvl(sailing_date,ARRIVAL_DATE),''01-01-1900''),''dd-mm-yyyy'') sailing_date,
       to_char(nvl(nvl(loading_date,ARRIVAL_DATE),''01-01-1900''),''dd-mm-yyyy'') loading_date,
       vpl.trade,
       case when vpl.trade=''ZIM'' then ''ZIM'' else ''GSL'' end trade_group,
       area,
       case when vpl.trade=''ZIM'' then ''ZIM'' else ''GSL'' end principal,
       vt.vessel_name,
       ct.country,
       ct.COUNTRY_NAME
       from TW.VOYAGE_PLAN vpl,
       TW.VOYAGE_PORTS vp,
       TW.PORT_TABLE PT,
       TW.COUNTRY_TABLE CT,
       TW.VESSEL_TABLE VT
       where vpl.vessel=rpad(ltrim(rtrim('''''+@ves+''''')),6)
       and vpl.voyage=rpad(ltrim(rtrim('''''+@voy+''''')),6)
       and ltrim(rtrim(vpl.leg))=ltrim(rtrim('''''+@leg+'''''))
       and VP.vessel=rpad(ltrim(rtrim('''''+@ves+''''')),6)
       and VP.voyage=rpad(ltrim(rtrim('''''+@voy+''''')),6)
       and ltrim(rtrim(VP.leg))=substring(ltrim(rtrim('''''+@leg+''''')),1,1)
       and ltrim(rtrim(VP.port_of_call))=ltrim(rtrim('''''+@port+'''''))
       and VP.PORT_SEQUENCE='''''+cast(@port_seq as varchar(10))+'''''
       and ltrim(rtrim(VP.port_of_call))=ltrim(rtrim('''''+@port+'''''))
       and ltrim(rtrim(pt.port_code))=ltrim(rtrim('''''+@port+'''''))
       AND substring(vp.port_of_call,1,2)=ct.country
       and vt.vessel=vp.vessel'') main')
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Friday, October 14, 2011 5:13 AM