OPENQUERY syntax errors (not recognizing CROSS APPLY)

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

LeogalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
LeogalAuthor Commented:
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
 
LeogalAuthor Commented:
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Dave FordSoftware Developer / Database AdministratorCommented:
Do you mean "cross join" ?
0
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
LeogalAuthor Commented:
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
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
In your main query, change
FROM [S1022466].[KBM400MFG].[FLVENDM]

Open in new window

to
FROM A

Open in new window

0
 
LeogalAuthor Commented:
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
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
LeogalAuthor Commented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
LeogalAuthor Commented:
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
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
LeogalAuthor Commented:
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
 
Shaun KlineLead Software EngineerCommented:
Glad to assist.
0
 
LeogalAuthor Commented:
Shaun has once again been invaluable help in teaching me more about OPENQUERY and its syntax.
0
All Courses

From novice to tech pro — start learning today.