Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OPENQUERY not recognizing valid table names

Posted on 2014-09-19
6
Medium Priority
?
445 Views
Last Modified: 2014-09-19
I truly need help with this production query issue. I have a Openquery that was working until I had to add a concatenated field.   I am stumped just what I am doing wrong.  The incoming data is  a DB2 table (FLVENDM) via a linked server (KBM400MFG).

Here is the query:

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1, A.VMADR2)

 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,''CONCAT(B.VMADR1,B.VMADR2)''
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window


Here is the error messages I get when trying to run the query:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'VMADR1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'VMADR2'.


I have stumbled into yet another problem. I can comment out the troublesome concat and move on but then I hit a new issue with SUBTRING , see the modified query & error below:

here is the query
 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  --,''CONCAT(B.VMADR1,B.VMADR2)''
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

this is the error:
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near ',REVERSE([VMADR3]),+14 ) )),'.


I have attached the DB2  file layout in the form of a simple query to this question. I am really wanting to use OPENQUERY as otherwise the query runs forever and a day.

Would someone be able to help me with this problem? It would be most appreciated, Thank you!
sql-select-vender-address-master.rtf
0
Comment
Question by:Leogal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:Leogal
ID: 40332888
In moving forward I find this error when attempting a substring in openquery....

here is the query
use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 --,CONCAT(A.VMADR1, A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 'URL'
	 ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]

 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  --,''CONCAT(B.VMADR1,B.VMADR2)''
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,ISNULL(SUBSTRING(B.[VMADR3],1, (CHARINDEX('' '',REVERSE(B.[VMADR3]),+14 ) )),'' '')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

here is the error

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "*N in *N type *N not found. SQLSTATE: 42704, SQLCODE: -204".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "
  SELECT 
       'Delivery'
	  ,B.VMCTRY 
	  , 'Delivery' 
	  ,B.VMNO
	  --,'CONCAT(B.VMADR1,B.VMADR2)'
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,'URL'
	  ,ISNULL(SUBSTRING(B.[VMADR3],1, (CHARINDEX(' ',REVERSE(B.[VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG". 

Open in new window

I am feeling very inept. I would truly appreciate any help on the concat & substring issues.
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 40332901
I would suggest not performing any string manipulation in your OPENQUERY. Bring back all of the columns needed, and then perform the manipulation on the SQL Server side.

In your first query, it would look like this:
SELECT 
      'Delivery' 
      ,A.VMCTRY
        ,'Delivery' 
        ,A.VMNO
        ,CONCAT(A.VMADR1, A.VMADR2)

  FROM OPENQUERY 
  (
  KBM400MFG, 
   '
   SELECT 
        ''Delivery''
         ,B.VMCTRY 
         , ''Delivery'' 
         ,B.VMNO
         ,B.VMADR1
         ,B.VMADR2
    FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   '
    ) AS A

Open in new window

The same would apply to your second query.
0
 

Author Closing Comment

by:Leogal
ID: 40332912
Shaun gave me invaluable help that is most appreciated.
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40332913
The errors you are receiving are most likely caused by DB2 not understanding the SQL Server functions you are attempting to use in the OPENQUERY statement. The OPENQUERY SQL should follow the source (in your case, DB2) "rules" for SQL.
0
 

Author Comment

by:Leogal
ID: 40332981
Shaun, DB2 seems to be a bit obstinate when writing an OPENQUERY.  I am having yet another issue and opened a new question.   I am truly a novice at OPENQUERY but I want to learn more about it as it is very fast and very powerful.
Thank so much for your kind and very helpful assistance!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to deal with a specific error when using the Enable-RemoteMailbox cmdlet to create a mailbox in the cloud-based service, for an existing user in an on-premises Active Directory.
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question