Solved

OPENQUERY syntax errors (not recognizing CROSS APPLY)

Posted on 2014-09-19
17
569 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
The reason that corporations and businesses use Windows servers is because it supports custom modifications to adapt to the business and what it needs. Most individual users won’t need such powerful options. Here I’ll explain how you can enable Wind…
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 steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now