AS400 LINKED SERVER FAILS ON INSERT/DELETE
-
I have successfully setup a linked server from my SQL Server to our AS400 that I can use for SELECT's and UPDATE's. However I can not get an INSERT or DELETE to work.
First what syntax should I be using. For the insert if I use:
INSERT INTO OPENQUERY([R2D2-Test], 'SELECT ABAN8,ABALPH FROM CRPDTA.F0101')
(ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.].
If I use:
INSERT INTO [R2D2-Test]..CRPDTA.F0101 (ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. 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='R2D2-Test', TableName='CRPDTA.F0101'].
I'm not sure if this indicates a config. issue on either the ODBC conn. or the linked server or if I have a syntax error.
For the DELETE, if I use
Delete from [R2D2-Test]..CRPDTA.F0101DAU where aban8 = 100
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. 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='R2D2-Test', TableName='CRPDTA.F0101'].
If I use:
Select * from openquery([R2D2-Test],'Delete from CRPDTA.F0101 where aban8 = 100')
I get:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Delete from CRPDTA.F0101 where aban8 = 100'. The OLE DB provider 'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='IBMDA400', Query=Delete from CRPDTA.F0101 where aban8 = 100'].
Can someone help figure out the correct configuration and/or syntax.
Help!!
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
Hi, This is a frustrating subject, indeed. I've had similar problems and there isn't much help out there.
What I did was use the IBM driver to create an ODBC connection, and then use the [Microsoft OLE/DB for ODBC] driver in the linked server.
You appear to be using IBMDA400 (which is an OLE/DB provider) directly in the linked server, bypassing ODBC. That's the way I think it SHOULD work, but I have never gotten it to actually work that way.
Anyway, try the ODBC way. And make sure that the tables you are trying to update have journaling turned on.
Interestingly, I've found the opposite to be true when connecting to UDB on a bigger IBM machine. Can't use ODBC but the OLE/DB provider (IBMDADB) works great.
good luck! jg
-
Hi Jeff,
I was using the IBM ODBC with the IBM DB2 UDB OLE DB in th linked server.
I have created a new ODBC and an new linked server using the MS OLE/DB for ODBC but am still having problems.
If I use the
Delete from [R2D2Test]..CRPDTA.F0101DAU where aban8 = 100
syntax I get:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
If I use the
Select * from openquery([R2D2TEST],'Delete from crpdta.f0101dau where aban8 = 100')
I get:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Delete from crpdta.f0101dau where aban8 = 100'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=Delete from crpdta.f0101dau where aban8 = 100'].
For the insert if I use
INSERT INTO OPENQUERY([R2D2TEST], 'SELECT * FROM CRPDTA.F0101 WHERE ABAN8 = 1')
(ABAN8, ABALPH)
sELECT 992004,'Test Insert from SQL'
I get
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - F0101 in CRPDTA not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].
if is use
INSERT INTO OPENQUERY([R2D2TEST], 'SELECT ABAN8,ABALPH FROM CRPDTA.F0101')
(ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - F0101 in CRPDTA not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].
Also, my insert no longer works. I use
uPDATE OPENQUERY ( [R2D2TEST],'SELECT * FROM CRPDTA.F0101 where aban8 =100')
SET ABALPH = 'GI Keg'
and get
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - F0101 in CRPDTA not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005: ].
Would it be possible to get screen shots of your ODBC and your linked server settings (particularly provider opt.s) and an indication of what syntax you use for updates, inserts and deletes.
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
I'm sorry but I don't have access to the machines until Tuesday. I will try to help in the meantime.
The 4 part name method (your first query) should be the right way to go. You are only specifying 1,3 and 4, though. Try putting in the correct value in position 2:
Delete from [R2D2Test].[fill in this blank].CRPDTA.F0101DAU where aban8 = 100
jg
-
And I forgot to mention that the SQL7008 error you might get (and did get in some of your queries) is probably because journaling is not set up on the target table in DB2. I believe that this is the equivalent of transaction logs in MS SQL. In UDB, it is optional and has to be explicitly turned on.
http://www-1.ibm.com/support/docview.wss?uid=nas10a335f5e521fdca486256df1005b4a50
jg
-
What should the 2nd param. be. In SQL it is the owner, but for a 400 query???
My current syntax has the LinkedServer as #1, Library as #3, and File as #4.
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
I was going to ask you where the heck is the library name? Do you have some SELECT statements that work?
I never did get the linked server thing to work right when I was doing 400 stuff. I opened one connection to SQL Server through ADO and one to the 400 through Client Access Express.
Seems as though your permissions to the 400 might not allow the user that you used for the link to do the inserts.
ATBCharles Kincaid
-
It would seem that the journalling was the issue. Once I journalled the file I was able to update it using either the whether I used the IBM DB2 UDB OLE DB or the MS OLE/DB for ODBC it now works, though the MS OLE/DB for ODBC seems to be considerably faster.
I don't know if this will work for me as the files that I want to insert to are provided by the application vendor.
Does anyone know if there is a way to configure this such that the file does NOT need to be journalled?
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
There are some configuration settings in the client access tools that can be made that affect the transaction isolation level that is used. This is supposed to allow updates without having journaling enabled...
BUT....
Like just about everything else related to AS400 data access, the documented methods don't seem to work for me.
At least you got somewhere! have a great weekend.
jg
-
I have same problem,
While updating AS 400 files message below is shown:
Server: Msg 7344, Level 16, State 2, Line 1
OLE DB provider 'MSDASQL' could not UPDATE table '[AS400_IZM].[S6503FBC].[PRDLIB].[PRODPLN]' because of column 'PLN004'. The data value violated the schema for the column.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
-
Hi Thinky,
That looks like a completely different error to me. The error message suggests that there is a data type mismatch between your source and destination. I think you should check the DDL for the PLN004 column and look at the data type that you are using in the update.
Perhaps you could post that information here.
-
I had the same problem and activating the Journaling on the table I need all insert/update statement where running correctly. Thank you.
-
Jeff Gray (6/11/2007)
Hi Thinky,
That looks like a completely different error to me. The error message suggests that there is a data type mismatch between your source and destination. I think you should check the DDL for the PLN004 column and look at the data type that you are using in the update.
Perhaps you could post that information here.
The OLE DB provider "MSDASQL" for linked server "links" indicates that either the object has no columns or the current user does not have permissions on that object.
what should i
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply