473,597 Members | 2,341 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Inventor yHistory
However, this insert statement fails:
insert into prod400db.TEST. MELDBF.Inventor yHistory
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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].[InventoryHistor y]'.
[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_ERRORSOCCU RRED, but none
of the columns is in error status. Data status sent to the provider:
[COLUMN_NAME=PLA NTID STATUS=DBSTATUS _S_OK], [COLUMN_NAME=ITE MNUMBER
STATUS=DBSTATUS _S_OK], [COLUMN_NAME=TRA NSACTIONTYPE
STATUS=DBSTATUS _S_OK], [COLUMN_NAME=TRA NSACTIONQTY STATUS=DBSTA...
This statement also fails:
insert into prod400db.TEST. MELDBF.InvHst
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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.In vHst'.
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='p rod400db',
TableName='TEST .MELDBF.InvHst'].

However, this statement works fine:
insert into prod400db.TEST. MELDBF.InvHstSQ L
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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(Prod4 00db,'select plantId, itemnumber,
transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem from
MELDBF.Inventor yHistory')
values ('100','CMW21', 'R',11, NULL, NULL, NULL, 'WIP',' ',
NULL,'DSB','CMW ')

Now, INVHST is the short system name for the InventoryHistor y table.
InvHstSQL is a view created over the InventoryHistor y 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 9438
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.Inventor yHistory
However, this insert statement fails:
insert into prod400db.TEST. MELDBF.Inventor yHistory
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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].[InventoryHistor y]'.
[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_ERRORSOCCU RRED, but none
of the columns is in error status. Data status sent to the provider:
[COLUMN_NAME=PLA NTID STATUS=DBSTATUS _S_OK], [COLUMN_NAME=ITE MNUMBER
STATUS=DBSTATUS _S_OK], [COLUMN_NAME=TRA NSACTIONTYPE
STATUS=DBSTATUS _S_OK], [COLUMN_NAME=TRA NSACTIONQTY STATUS=DBSTA...
This statement also fails:
insert into prod400db.TEST. MELDBF.InvHst
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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.In vHst'.
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='p rod400db',
TableName='TEST .MELDBF.InvHst'].

However, this statement works fine:
insert into prod400db.TEST. MELDBF.InvHstSQ L
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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(Prod4 00db,'select plantId, itemnumber,
transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem from
MELDBF.Inventor yHistory')
values ('100','CMW21', 'R',11, NULL, NULL, NULL, 'WIP',' ',
NULL,'DSB','CMW ')

Now, INVHST is the short system name for the InventoryHistor y table.
InvHstSQL is a view created over the InventoryHistor y 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.ne t>, 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.Inventor yHistory

insert into prod400db.TEST. MELDBF.InvHstTm p
(plantId, itemnumber, transactionType , transactionQty,
fromStockRoom, fromStatusCode, fromBinLocation , toStockRoom,
toStatusCode, toBinLocation,
transactionReas on, transactionSour ceSystem)
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
9563
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 statement : Select * from Orcldb..SYSTEM.CrossTabSaveSites and get a return result. The problem I realize is that some of the tables are going to be quite larger and I can't wait for SQL Server to download all the data and parse it itself, so...
5
17867
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 linked tables, and my codes is as follows: Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog) when this line is executed I get the error: "Run-time error '3622':
1
8042
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 records and validate added data. My question is..
0
1575
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 2000) as follows USERS TABLE
14
10121
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 I should give back to the community by posting our findings. Thanks you all for all your help till now by posting problems and their solutions. ~Abhijit
2
6068
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 successful. Please help. Thanks Kai
1
2881
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 correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application is MS Access 2000. The back-end is MS SQL Server 2000. After I migrated the database and
2
4162
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 reason I did this is because we are using a stored procedure on Server2 to send mail, as we have found that using mail on 2000 doesn't always work as advertised.
0
1589
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 files on an iSeries (AS/400, System i5, or whatever IBM calls it today), version v5r4, and would like to permanently migrate them to a DB2 UDB for Linux v8 system. All of these files are used by native iSeries programs (written mostly in CL and...
0
7971
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8259
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5436
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3889
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3932
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1495
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1243
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.