Solved

OPENQUERY syntax errors (not recognizing CROSS APPLY)

Posted on 2014-09-19
17
601 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
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…

770 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