Answered by:
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
- Edited by Jackson_1990 Friday, October 14, 2011 5:09 AM
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 ContributorFriday, October 14, 2011 4:00 AM -
Yes, by openquery
Many Thanks & Best Regards, HuaMin ChenFriday, 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.comFriday, 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- Edited by Jackson_1990 Friday, October 14, 2011 4:14 AM
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 ChenFriday, 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.
- Edited by Jonathan Quek Friday, October 14, 2011 5:03 AM
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.comFriday, October 14, 2011 5:13 AM