Solved

OPENQUERY INSERT not recognizing linked server

Posted on 2014-09-19
12
541 Views
Last Modified: 2014-09-23
I have created a sql script to insert data into local database table using an OPENQUERY against a DB2 table.
I know that the linked server and field names are okay as a SELECT script validated this.  

When I flopped my code to become an INSERT into statement using OPENQUERY i received this error message.

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  
                         
                          ,VMCTRY
                          ,VMNO
                          ,VMADR1
                          ,VMPFXN
                          ,VMPPHN
                          ,VMADR2
                          ,VMADR3                                    
                          ,VMCOUN  
                           ,VMPZIP  

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



What am I missing?  Below is my query code that is producing the error.  Any help given will be appreciated.




INSERT INTO DMFVENDORADDRESSENTITY2

 	SELECT		  'Delivery' as [AddressName]
				  ,VMCTRY    as [CountryRegionId]
				  ,'Delivery'as [LocationRole]
				  ,VMNO as [VendAccountNum]
				  ,ISNULL(VMADR1,' ' ) as [Address]
				  ,' ' as [AddressEmail]
				  ,ISNULL(VMPFXN,' ') as [AddressFax]
				  ,ISNULL(VMPPHN,' ') as [AddressPhone]
				  ,' ' as [AddressTelexName]
				  ,' ' as [AddressUrl] 
				  ,'URL' as [AddressUrlName]
				  ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2))) AS DeliveryAddress
									 ,ISNULL(SUBSTRING(VMADR3, 1, CHARINDEX(' ',REVERSE(VMADR3), 14)), ' ') AS [City]
						 
					,VMCOUN as [County]
					 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
										  ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
					,VMPZIP as [ZipCode]

  
  FROM OPENQUERY 
      (KBM400MFG,

	 ' SELECT  
				  
				  ,VMCTRY
				  ,VMNO 
				  ,VMADR1
				  ,VMPFXN
				  ,VMPPHN
				  ,VMADR2
				  ,VMADR3						 
				  ,VMCOUN  
				   ,VMPZIP  

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

Open in new window

0
Comment
Question by:Leogal
  • 7
  • 5
12 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40333481
You can't have a comma before the first column name:

SELECT  
                         
                          ,VMCTRY

should be:

SELECT  
                         
                          VMCTRY


And, to be safe, I'd add an alias name after the ) of the OPENQUERY:
...
FROM  [KBM400MFG].[FLVENDM]
   '
   )  AS oq
CROSS APPLY ...
0
 

Author Comment

by:Leogal
ID: 40333767
I must be getting tired to have missed the comma on the first element.  I have progressed , but have new errors.

The errors are:
Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'CROSS'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near 'A-Z'.


the query is :

USE [DmStagingVD]
			
  UPDATE OPENQUERY([KBM400MFG],
   
	 'SELECT(
				    RTRIM(VMPAD1) + RTRIM(LTRIM(VMPAD2)) AS [ADDRESS]
				   ,'' '' as [ADDRESSEMAIL]
				   ,''Pay'' as [ADDRESSEMAILNAME]
				   ,ISNULL(VMPFXN,'' '') as [ADDRESSFAX]
				   ,'' '' as [ADDRESSFAXNAME]
				   ,'' '' as [ADDRESSNAME]
				   ,ISNULL(VMPPHN,'' '')as [ADDRESSPHONE]
				   ,'' '' as [ADDRESSPHONENAME]
				   ,'' '' as [ADDRESSTELEX]
				   ,'' '' as [ADDRESSTELEXNAME]
				   ,'' '' as [ADDRESSURL]
				   ,''URL'' as [ADDRESSURLNAME]
				   ,'' '' as [BUILDINGCOMPLIMENT]
				   ,ISNULL(SUBSTRING(VMPAD3, 1, CHARINDEX('' '',REVERSE(VMPAD3), 14)), '' '') AS  [CITY]
				   ,ISNULL(VMCTRY,'' '') as [COUNTRYREGIONID]
				   ,ISNULL(VMCOUN,'' '') as [COUNTY]
				   ,'' '' as [DEFINITIONGROUP]
				   ,'' '' as [DISTRICTNAME]
				   ,'' '' as [EXECUTIONID]
				   ,1 as [ISSELECTED]
				   ,0 as [LATITUDE]
				   ,'' '' as [LOCATIONID]
				   ,''Pay'' as [LOCATIONROLE]
				   ,0 as [LONGITUDE]
				   ,'' '' as [PARTYNUMBER]
				   ,'' '' as [PHONELOCAL]
				   ,'' '' as [POSTBOX]
				    ,CASE WHEN VMPAD3_start_of_state < 2 THEN '''' 
								ELSE SUBSTRING(VMPAD3, VMPAD3_start_of_state, 2) END AS [STATE]
				   ,'' '' as [STREET]
				   ,'' '' as [STREETNUMBER]
				   ,0 as [TIMEZONE]
				   ,0 as [TRANSFERSTATUS]
				   ,'' '' as [VALIDFROM]
				   ,'' '' as [VALIDFROMTZID]
				   ,'' '' as [VALIDTO]
				   ,'' '' as [VALIDTOTZID]
				   ,ISNULL(VMPAEE,'' '') as [VENDACCOUNTNUM]
				   ,ISNULL(VMPZIP,'' '') as [ZIPCODE]
				   ,1 as [RECVERSION]
				   ,1 as [PARTITION]
				   ,1 as [RECID]		   

					 
   FROM  [KBM400MFG].[FLVENDM] AS A
   ')  
    CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2	

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40333797
Move the CROSS APPLY to within the OPENQUERY():
 FROM  [KBM400MFG].[FLVENDM] AS A
    CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
   ')  

I'd also add an alias just to be safe, even though technically you may not have to:
 FROM  [KBM400MFG].[FLVENDM] AS A
    CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
   ') AS alias_name

Finally, you can't UPDATE an OPENQUERY.  If you want to do an UPDATE rather than a SELECT * FROM, you need to use EXEC ... AT [servername]
0
 

Author Comment

by:Leogal
ID: 40333807
Scott I was on the same path.

Now I am receiving this error:
Msg 102, Level 15, State 1, Line 55
Incorrect syntax near ')'.


here is the modified query
USE [DmStagingVD]
			



  UPDATE OPENQUERY([KBM400MFG],
   
	 'SELECT(
				    RTRIM(VMPAD1) + RTRIM(LTRIM(VMPAD2)) AS [ADDRESS]
				   ,'' '' as [ADDRESSEMAIL]
				   ,''Pay'' as [ADDRESSEMAILNAME]
				   ,ISNULL(VMPFXN,'' '') as [ADDRESSFAX]
				   ,'' '' as [ADDRESSFAXNAME]
				   ,'' '' as [ADDRESSNAME]
				   ,ISNULL(VMPPHN,'' '')as [ADDRESSPHONE]
				   ,'' '' as [ADDRESSPHONENAME]
				   ,'' '' as [ADDRESSTELEX]
				   ,'' '' as [ADDRESSTELEXNAME]
				   ,'' '' as [ADDRESSURL]
				   ,''URL'' as [ADDRESSURLNAME]
				   ,'' '' as [BUILDINGCOMPLIMENT]
				   ,ISNULL(SUBSTRING(VMPAD3, 1, CHARINDEX('' '',REVERSE(VMPAD3), 14)), '' '') AS  [CITY]
				   ,ISNULL(VMCTRY,'' '') as [COUNTRYREGIONID]
				   ,ISNULL(VMCOUN,'' '') as [COUNTY]
				   ,'' '' as [DEFINITIONGROUP]
				   ,'' '' as [DISTRICTNAME]
				   ,'' '' as [EXECUTIONID]
				   ,1 as [ISSELECTED]
				   ,0 as [LATITUDE]
				   ,'' '' as [LOCATIONID]
				   ,''Pay'' as [LOCATIONROLE]
				   ,0 as [LONGITUDE]
				   ,'' '' as [PARTYNUMB
				   ,'' '' as [PHONELOCAL]
				   ,'' '' as [POSTBOX]
				    ,CASE WHEN VMPAD3_start_of_state < 2 THEN '''' 
								ELSE SUBSTRING(VMPAD3, VMPAD3_start_of_state, 2) END AS [STATE]
				   ,'' '' as [STREET]
				   ,'' '' as [STREETNUMBER]
				   ,0 as [TIMEZONE]
				   ,0 as [TRANSFERSTATUS]
				   ,'' '' as [VALIDFROM]
				   ,'' '' as [VALIDFROMTZID]
				   ,'' '' as [VALIDTO]
				   ,'' '' as [VALIDTOTZID]
				   ,ISNULL(VMPAEE,'' '') as [VENDACCOUNTNUM]
				   ,ISNULL(VMPZIP,'' '') as [ZIPCODE]
				   ,1 as [RECVERSION]
				   ,1 as [PARTITION]
				   ,1 as [RECID]		   

					 
   FROM  [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2	
 ' ) 

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40333810
Interesting, OPENQUERY function not statement.

It seems, then, that you're just missing the "SET" part of the UPDATE:

USE [DmStagingVD]
                  


  UPDATE OPENQUERY([KBM400MFG],
 
       'SELECT(
                            RTRIM(VMPAD1) + RTRIM(LTRIM(VMPAD2)) AS [ADDRESS]
                           ,'' '' as [ADDRESSEMAIL]
                           ,''Pay'' as [ADDRESSEMAILNAME]
                           ,ISNULL(VMPFXN,'' '') as [ADDRESSFAX]
                           ,'' '' as [ADDRESSFAXNAME]
                           ,'' '' as [ADDRESSNAME]
                           ,ISNULL(VMPPHN,'' '')as [ADDRESSPHONE]
                           ,'' '' as [ADDRESSPHONENAME]
                           ,'' '' as [ADDRESSTELEX]
                           ,'' '' as [ADDRESSTELEXNAME]
                           ,'' '' as [ADDRESSURL]
                           ,''URL'' as [ADDRESSURLNAME]
                           ,'' '' as [BUILDINGCOMPLIMENT]
                           ,ISNULL(SUBSTRING(VMPAD3, 1, CHARINDEX('' '',REVERSE(VMPAD3), 14)), '' '') AS  [CITY]
                           ,ISNULL(VMCTRY,'' '') as [COUNTRYREGIONID]
                           ,ISNULL(VMCOUN,'' '') as [COUNTY]
                           ,'' '' as [DEFINITIONGROUP]
                           ,'' '' as [DISTRICTNAME]
                           ,'' '' as [EXECUTIONID]
                           ,1 as [ISSELECTED]
                           ,0 as [LATITUDE]
                           ,'' '' as [LOCATIONID]
                           ,''Pay'' as [LOCATIONROLE]
                           ,0 as [LONGITUDE]
                           ,'' '' as [PARTYNUMB
                           ,'' '' as [PHONELOCAL]
                           ,'' '' as [POSTBOX]
                            ,CASE WHEN VMPAD3_start_of_state < 2 THEN ''''
                                                ELSE SUBSTRING(VMPAD3, VMPAD3_start_of_state, 2) END AS [STATE]
                           ,'' '' as [STREET]
                           ,'' '' as [STREETNUMBER]
                           ,0 as [TIMEZONE]
                           ,0 as [TRANSFERSTATUS]
                           ,'' '' as [VALIDFROM]
                           ,'' '' as [VALIDFROMTZID]
                           ,'' '' as [VALIDTO]
                           ,'' '' as [VALIDTOTZID]
                           ,ISNULL(VMPAEE,'' '') as [VENDACCOUNTNUM]
                           ,ISNULL(VMPZIP,'' '') as [ZIPCODE]
                           ,1 as [RECVERSION]
                           ,1 as [PARTITION]
                           ,1 as [RECID]              

                              
   FROM  [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
 ' )
SET <column_name> = <new_value> --,...
0
 

Author Comment

by:Leogal
ID: 40333838
Ok I see now, I will modify my query to add the set statement with multiple columns and values like this:

   FROM  [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
 
SET <column_name> = <new_value> ,
       <column_name> = <new_value> ,
       <column_name> = <new_value> ,
       <column_name> = <new_value> ,
      <column_name> = <new_value> 

Open in new window


I will leave the select statement as is...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Leogal
ID: 40333847
inside the query I will only use the appropriate field names that I use, outside the openquery i will show my concat and ' ' and so forth
0
 

Author Comment

by:Leogal
ID: 40333880
I have made all the changes and now I am receiving a new 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 5
An error occurred while preparing the query "SELECT
	   (
                            VMPAD1 
			   ,VMPAD2 
                           ,VMPFXN 
                           ,VMPPHN 
                           ,VMPAD3
                           ,VMCTRY
                           ,VMCOUN
                           ,VMPAEE
                           ,VMPZIP
                           

                               
   FROM  [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', [VMPAD3] + ' ') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
 " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG". 

Open in new window

I am trying to figure out where I went wrong on the syntax...

here is the modified query:
USE [DmStagingVD]
                  


  UPDATE OPENQUERY([KBM400MFG],
  
       'SELECT
	   (
                            VMPAD1 
			   ,VMPAD2 
                           ,VMPFXN 
                           ,VMPPHN 
                           ,VMPAD3
                           ,VMCTRY
                           ,VMCOUN
                           ,VMPAEE
                           ,VMPZIP
                           

                               
   FROM  [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX(''%[ ][A-Z][A-Z] %'', [VMPAD3] + '' '') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
 ' ) 
 
 SET [ADDRESS] = RTRIM(VMPAD1) + RTRIM(LTRIM(VMPAD2)),
     [ADDRESSEMAIL] = ' ' ,
     [ADDRESSEMAILNAME] = 'Pay',
     [ADDRESSFAX] = VMPFXN,
     [ADDRESSFAXNAME]= ' ',
     [ADDRESSNAME] = ' ' ,
     [ADDRESSPHONE] = VMPPHN,
     [ADDRESSPHONENAME] = ' ' ,
     [ADDRESSTELEX] = ' ',
     [ADDRESSTELEXNAME] =' ',
     [ADDRESSURL] = ' ', 
     [ADDRESSURLNAME]=' ' ,
     [BUILDINGCOMPLIMENT] = ' ',
     [CITY] = ISNULL(SUBSTRING(VMPAD3, 1, CHARINDEX('  ',REVERSE(VMPAD3), 14)), ' '),
     [COUNTRYREGIONID] = ISNULL(VMCTRY,' '),
     [COUNTY] = ISNULL(VMCOUN,' '),
	 [DEFINITIONGROUP] = ' ',
     [DISTRICTNAME] = ' ',
     [EXECUTIONID] = ' ',
     [ISSELECTED] = 1,   
     [LATITUDE] = 0 ,    
     [LOCATIONID] = ' ',
     [LOCATIONROLE] = 'Pay' ,
     [LONGITUDE] = 0,
     [PARTYNUMB] = ' ',
     [PHONELOCAL] = ' ',
     [POSTBOX] =' ',
     [STATE] = (CASE WHEN VMPAD3_start_of_state < 2 THEN ' ' ELSE SUBSTRING(VMPAD3, VMPAD3_start_of_state, 2)end),
     [STREET] = ' ',
     [STREETNUMBER] = ' ',
     [TIMEZONE] = 0,
     [TRANSFERSTATUS] = 0,
     [VALIDFROM] = ' ',
     [VALIDFROMTZID] = ' ',
     [VALIDTO] = ' ',
     [VALIDTOTZID] = ' ',
     [VENDACCOUNTNUM] = ISNULL(VMPAEE,' '),
     [ZIPCODE] = ISNULL(VMPZIP,' '),
     [RECVERSION] = 1,
     [PARTITION] = 1,                    
     [RECID]    = 1    

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40333884
Get rid of the open paren after the SELECT:


       'SELECT
        --( --<<--
0
 

Author Comment

by:Leogal
ID: 40333889
I removed the open paren after Select and am still getting the 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 5
An error occurred while preparing the query "SELECT
        
                            A.VMPAD1
                                       ,A.VMPAD2
                           ,A.VMPFXN
                           ,A.VMPPHN
                           ,A.VMPAD3
                           ,A.VMCTRY
                           ,A.VMCOUN
                           ,A.VMPAEE
                           ,A.VMPZIP
                           

                               
   FROM   [KBM400MFG].[FLVENDM] As A  
      CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', [VMPAD3] + ' ') + 1 AS VMPAD3_start_of_state) AS StateFinder2      
 " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400M
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40333894
If the target db is DB2, DB2 does not support CROSS APPLY.  You'll need to compute that value differently.
0
 

Author Comment

by:Leogal
ID: 40333901
the target is a local table in the database DmStagingVD....
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

13 Experts available now in Live!

Get 1:1 Help Now