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
" 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]
,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]
CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', [VMADR3] + ' ') + 1 AS VMADR3_start_of_state) AS StateFinder