Solved

OPENQUERY syntax errors (not recognizing CROSS APPLY)

Posted on 2014-09-19
17
614 Views
Last Modified: 2014-09-19
I am definitely a novice with OPENQUERY.  I was recently helped here no how to use concat & substring with OPENQUERY now I am stumped with how to use CROSS APPLY.  This is a production issue so any assistance is gladly welcomed.


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]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   	 CROSS APPLY (
		SELECT PATINDEX('' ''%[ ][A-Z][A-Z] %'' '', C.VMADR3 + '' '') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1
  '
   ) AS A

Open in new window

here is the error I am receiving:

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "
  SELECT 
       'Delivery'
	  ,B.VMCTRY 
	  , 'Delivery' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,'URL'
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   	 CROSS APPLY (
		SELECT PATINDEX(' '%[ ][A-Z][A-Z] %' ', C.VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".

Open in new window

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
  • 8
  • 7
17 Comments
 

Author Comment

by:Leogal
ID: 40333026
Following Shaun Kline's advice from a prior OPENQUERY issue I have modified my query as follows:
use DmStagingVD. Now I have syntax issue that I thought a second set of eyes may help me spot the syntax issue.

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]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

	FROM [S1022466].[KBM400MFG].[FLVENDM]
	 	 CROSS APPLY (
		SELECT PATINDEX('%[ ][A-Z][A-Z] %', 'VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1 


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B 
  '
   ) AS A

Open in new window


here is the error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1 


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ''.
Msg 105, Level 15, State 1, Line 49
Unclosed quotation mark after the character string '
   ) AS A

Open in new window

0
 

Author Comment

by:Leogal
ID: 40333044
oaky I whittled it down to this syntax error

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,A.VMADR1 
	 ,A.VMADR2 
	 ,A.VMPFXN
	 ,A.VMPPHN
	 'URL'
	 ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

	FROM [S1022466].[KBM400MFG].[FLVENDM]
	 	 CROSS APPLY (
		SELECT PATINDEX('%[ ][A-Z][A-Z] %', VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1  


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B 
  '
   ) AS A

Open in new window


Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'FROM'.

I am trying to figure out what I am missing.
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40333055
Do you mean "cross join" ?
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 500 total points
ID: 40333076
First, a comment: When posting SQL, use the Code block in the editor, as this puts your SQL into a scrollable window and reduces the overall page length.

Now, about that SQL. Try running a SELECT * FROM OPENQUERY(...) so you can eliminate issues when manipulating returned values. If 'Delivery' and 'URL' are just static text you need in the output, do not put them in the open query SQL, as this is just extra stuff the source server needs to send back to you. If you truly do have a linked server set up named "KBM400MFG", then it may be that you have your table name incorrect. In SQL Server, you identify a table as: <server name>.<database name>.<schema name>.<table name>.

Also, if I recall correctly, I do not believe you need to include the server name in the open query SQL, just the database, schema and table.
0
 

Author Comment

by:Leogal
ID: 40333132
I have modified my sql and resolved all issues in syntax except on last one:

the error is :
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'FROM'.

the query is:

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1,A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 ,'URL'
	 ,ISNULL(SUBSTRING( A.VMADR3 ,1, (CHARINDEX(' ',REVERSE(A.VMADR3 ),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	 ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(A.VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(A.VMADR3, 1, CHARINDEX(' ',REVERSE(A.VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(A.VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(B.VMADR2))) AS ADDRESS
	 ,A.VMPZIP

	 FROM [S1022466].[KBM400MFG].[FLVENDM]  
	     CROSS APPLY  (
        SELECT PATINDEX('%[ ][A-Z][A-Z] %', B.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state
 )AS assign_alias_1
 
 FROM OPENQUERY 

 ( 
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  ,''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [KBM400MFG].[FLVENDM] AS B 
                 

  '
   ) AS A
 



 

Open in new window

0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 500 total points
ID: 40333142
In your main query, change
FROM [S1022466].[KBM400MFG].[FLVENDM]

Open in new window

to
FROM A

Open in new window

0
 

Author Comment

by:Leogal
ID: 40333180
Scott I have made the change to the first from statement and removed the static fields from the inner query.
I am still getting an error, albeit small it is a roadblock.

The error is:
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'FROM'.


 I know it is a syntax issue yet I cannot figure this out, please help.

 use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1,A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 ,'URL'
	 ,ISNULL(SUBSTRING( A.VMADR3 ,1, (CHARINDEX(' ',REVERSE(A.VMADR3 ),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(A.VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(A.VMADR3, 1, CHARINDEX(' ',REVERSE(A.VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(A.VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(B.VMADR2))) AS ADDRESS
	 ,A.VMPZIP

	  FROM A
	     CROSS APPLY  (
        SELECT PATINDEX('%[ ][A-Z][A-Z] %', B.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state
 )AS assign_alias_1

 FROM OPENQUERY 

 ( 
 KBM400MFG, 
  '
  SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [KBM400MFG].[FLVENDM] AS B 


  '
   ) AS A

Open in new window

0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 500 total points
ID: 40333185
Let's start small and work our way up. Try this query first:
SELECT *
FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

This query will allow you to verify you are getting data back correctly.
0
 

Author Comment

by:Leogal
ID: 40333199
here are the query & resulting error:

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".

Open in new window

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40333213
That would indicate that the query is invalid on the DB2 server. My guess would be how the table is named. From my limited experience calling DB2 tables from SQL Server, you need a 3 part table name: database name/catalog, schema name and table name. This Microsoft page may help.
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40333223
Once you get the query corrected, you can take the following steps:
1.) Add in the CROSS APPLY.
2.) Manipulate the returned columns.

For the CROSS APPLY, the SQL goes after the OPEN QUERY statement. It would look something like this:
SELECT *
FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A
       CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', A.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state) AS StateFinder

Open in new window

0
 

Author Comment

by:Leogal
ID: 40333225
When I take the alias off as shown below the query works?

SELECT *
FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMADR3
	  ,VMCOUN
	  ,VMPAD3
	  ,VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 500 total points
ID: 40333245
Since you were only querying one table, the alias would not be needed in the OPEN QUERY portion. If it works, move on to step two!
0
 

Author Comment

by:Leogal
ID: 40333252
I have been successful in getting the query to work !

Here is the query:

SELECT 
   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMCOUN
	  ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(VMADR3, 1, CHARINDEX(' ',REVERSE(VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2))) AS ADDRESS

        ,VMPZIP

FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMADR3
	  ,VMCOUN
	  ,VMPAD3
	  ,VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A
         CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', A.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state) AS StateFinder

Open in new window


Shaun, thank you so very much for your assistance.  I have learned new methods that are cleaner and have a bit better handle on OPENQUERY.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40333253
Glad to assist.
0
 

Author Closing Comment

by:Leogal
ID: 40333257
Shaun has once again been invaluable help in teaching me more about OPENQUERY and its syntax.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article will review the basic installation and configuration for Windows Software Update Services (WSUS) in a Windows 2012 R2 environment.  WSUS is a Microsoft tool that allows administrators to manage and control updates to be approved and ins…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
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.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

756 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