473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Error inserting into iSeries DB2 table with long name via SQL Server 2000 linked Server

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
Jan 20 '06 #1
2 9389
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?

These are a lot of dots...
I'm not sure if DB2 for iSeries supports specification of the database
name (TEST). Will SQL server accept a three part name
(server.schema.table)? Just throwing a dart....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 21 '06 #2
In article <43*************@individual.net>, sr*****@ca.ibm.com says...
These are a lot of dots...
I'm not sure if DB2 for iSeries supports specification of the database
name (TEST). Will SQL server accept a three part name
(server.schema.table)? Just throwing a dart....

Cheers
Serge


Serge,

Thanks for the reply. But SQL Server needs the 4-part name. I get an
invalid name error with only 3 parts.

I'd imagine that SQL server is stripping out the parts DB2 on the
iSeries doesn't accept.

Remember, the following work:
select * from prod400db.test.meldbf.InventoryHistory

insert into prod400db.TEST.MELDBF.InvHstTmp
(plantId, itemnumber, transactionType, transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom,
toStatusCode, toBinLocation,
transactionReason, transactionSourceSystem)
values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP',' ',
NULL,'DSB','CMW')
I opened a PMR (35926,082) with IBM, they were able to recreate the
problem:
"The OLE DB zz trace shows the statement sent to us that way from SQL
Server. We can't see anything before it that indicates our OLE DB
provider gave it to them wrong. If it is a OLE DB problem, we think
Microsoft needs to tell us where they are getting it from.
Our links in the Support Center to Microsoft are usually done through
our customers. We do not normally have any direct links. Our
preference would be for you to contact them. We would be happy to get
on the phone with them if needed, or communicate through e-mail.
Please let us know what you would like to do."

The problem is I'm not on the PC side and wouldn't normally be involved
in support requests to MS. Our PC guys currently have other higher
priority problems to deal with.

Thanks,
Charles Wilt
Jan 23 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jevon | last post by:
I have used Linked Servers to hook up to an Oracle 9i database in SQL Server 2000. I can see all the Tables and Views when I look in the linked server section. I can also run the following SQL...
5
by: Philippa | last post by:
I'm trying to access data in vba using the openrecordset command. The data in on a SQL Server 2000 database, and I have linked tables to that data. the Table I'm trying to access is one of these...
1
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan...
0
by: Alistair | last post by:
Hi all, I am creating a database based site that keeps track of books, who has read them and the comments they have. After a little help in M.P.I.asp.DB I managed to create a database (access...
14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
2
by: kai | last post by:
Hi, All I used to link a SQL Server 2000 table through ODBC in Access 2003 using Wizard, now because the security requirement, I want to do the same job using code. I tried hard, but not...
1
by: bonnie.tangyn | last post by:
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled...
2
by: sqlgirl | last post by:
Hi, We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it is not up to date). Server2 is a 2005 box, SP2. I set up Server1 (2000) to have a linked server to Server2 (2005). The...
0
by: c0d3lib | last post by:
I am attempting to implement, what I believe to be, an unusual scenario, and am hoping to find someone that has experience with something similar. I currently have a series of physical and logical...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.