Incorrect syntax near '+'.

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

Answer 1

Are you using dynamic sql ??

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

Yes, by openquery

---

Many Thanks & Best Regards, HuaMin Chen

Answer 3

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

Answer 4

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

Answer 5

Hi,

I think openquery doesnt accept variables.

Hope this helps.

~ J

Answer 6

It seems to be not so good!

---

Many Thanks & Best Regards, HuaMin Chen

Answer 7

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.

 

 

Answer 8

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

Answer 9

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