× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have a linked server set up in SQL Server 2000 that I use to access my
iSeries.
OS/400 is at v5r3
iSeries Access is atv5r3 also.

The following select works fine:

select * from prod400db.test.meldbf.InventoryHistory


However, this insert statement fails:
insert into prod400db.TEST.MELDBF.InventoryHistory
 (plantId, itemnumber, transactionType, transactionQty, 
  fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom,
toStatusCode, toBinLocation,
  transactionReason, transactionSourceSystem)
values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP','  ',
NULL,'DSB','CMW')


With the following message:
Server: Msg 7343, Level 16, State 2, Line 1
OLE DB provider 'IBMDASQL' could not INSERT INTO table
'[prod400db].[TEST].[MELDBF].[InventoryHistory]'. 
[OLE/DB provider returned message: SQL0104: Token . was not valid. Valid
tokens: <IDENTIFIER>.
Cause . . . . . :   A syntax error was detected at token ..  Token . is
not a valid token.  A partial list of valid tokens is <IDENTIFIER>.
This list assumes that the statement is correct up to the token.  The
error may be earlier in the statement, but the syntax of the statement
appears to be valid up to this point. Recovery  . . . :   Do one or more
of the following and try the request again: -- Verify the SQL statement
in the area of the token .. Correct the statement.  The error could be a
missing comma or quotation mark, it could be a misspelled word, or it
could be related to the order of clauses. -- If the error token is
<END-OF-STATEMENT>, correct the SQL statement because it does not end
with a valid clause.]
OLE DB error trace [OLE/DB Provider 'IBMDASQL' IRowsetChange::InsertRow
returned 0x80040e21:  The provider return DB_E_ERRORSOCCURRED, but none
of the columns is in error status. Data status sent to the provider:
[COLUMN_NAME=PLANTID STATUS=DBSTATUS_S_OK], [COLUMN_NAME=ITEMNUMBER
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=TRANSACTIONTYPE
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=TRANSACTIONQTY STATUS=DBSTA...


This statement also fails:
insert into prod400db.TEST.MELDBF.InvHst
 (plantId, itemnumber, transactionType, transactionQty, 
  fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom,
toStatusCode, toBinLocation,
  transactionReason, transactionSourceSystem)
values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP','  ',
NULL,'DSB','CMW')

With this error:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'prod400db' does not contain table 'TEST.MELDBF.InvHst'.
The table either does not exist or the current user does not have
permissions on that table.
OLE DB error trace [Non-interface error:  OLE DB provider does not
contain the table: ProviderName='prod400db',
TableName='TEST.MELDBF.InvHst'].

However, this statement works fine:
insert into prod400db.TEST.MELDBF.InvHstSQL
 (plantId, itemnumber, transactionType, transactionQty, 
  fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom,
toStatusCode, toBinLocation,
  transactionReason, transactionSourceSystem)
values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP','  ',
NULL,'DSB','CMW')

Lastly, the openquery version of the insert into the long table name
works fine:
insert into openquery(Prod400db,'select plantId, itemnumber,
transactionType, transactionQty, 
  fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom,
toStatusCode, toBinLocation,
  transactionReason, transactionSourceSystem from
MELDBF.InventoryHistory')
values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP','  ',
NULL,'DSB','CMW')

Now, INVHST is the short system name for the InventoryHistory table.
InvHstSQL is a view created over the InventoryHistory table.  

I worked with IBM and did some tracing at both the OLEDB driver level
and the Ethernet Packets at the iSeries.  From the traces, it is
apparent that SQL server is incorrectly formating the data it passes to
the OLEDB driver when the target of the insert has a long name.
Addionally, Oracle has no problem inserting into the long table name via
a Database Link to the iSeries using the same OLEDB driver.

Note, I'm assuming that the reason the reason SQL server returns a not
found when I tried to use the short system name is that SQL server can
only see the table via one name.  A select using the short version of
the table gives the same not found error.

Since the InvHstSQL name of the view is <= 10 char, the long name and
the short name are the same. I assume that this has something to do with
why SQL doesn't have a problem.

Has anyone else run into this?  How did you fix it or are you using the
same work around I am?

Thanks,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.