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

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

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.
> 
>