"Charles Wilt" wrote:

> I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access
> via a linked server from SQL Server 2000.
>
> 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
>
>
>

> what happens if you manually log into the as400, open strsql and input a test
> insert query?
> Are you using client access? And if so is the hex conversion checked under
> the ODBC settings?
>