By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.