RE: Error inserting into iSeries DB2 table with long name via SQL Server2000 linked Server

So nobody else has run into this?

Are you using long names on your iSeries tables?

Anybody out there with an SQL server they could try this on?

Thanks,

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

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
> Sent: Friday, January 20, 2006 3:27 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Error inserting into iSeries DB2 table with long 
> name via SQL Server2000 linked Server
> 
> 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
>  
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>