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



Does the case match on the short name? It doesn't in your example. Not sure whether that will matter.

OTOH, if you are interested in pulling from the iSeries instead of pushing to it, give me a call. (verndor response). Sometimes that is not an option, as the data is changed at the SQL Server box, and that is the one in control. But who knows?

Vern
888.rjs.soft

At 06:47 AM 1/23/2006, you wrote:

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

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.