Leogal
asked on
OPENQUERY INSERT not recognizing linked server
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.
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
ASKER
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 :
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
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]
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]
ASKER
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
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
' )
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> --,...
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> --,...
ASKER
Ok I see now, I will modify my query to add the set statement with multiple columns and values like this:
I will leave the select statement as is...
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>
I will leave the select statement as is...
ASKER
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
ASKER
I have made all the changes and now I am receiving a new error:
here is the modified query:
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".
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
Get rid of the open paren after the SELECT:
'SELECT
--( --<<--
'SELECT
--( --<<--
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the target is a local table in the database DmStagingVD....
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 ...