AS400 LINKED SERVER FAILS ON INSERT/DELETE

[DonU]

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

 

[dvannoy]

I have setup a linked server to a 400 and never could get the IBMDA400 drivers to work correctly. I currently use MS OLEDB Provider for ODBC in my linked server properties. then in my system dsn i use the client access 32bit odbc driver. there are a few settings in your odbc that need to be setup correctly. such as the default library, your connection type and scrollable cursor. enable lazy close support should not be checked.

hope this helps

 

[DonU]

Hi

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.

 

[dvannoy]

I can give you screen shots of my settings. at this point I am not writing back to our 400. have you tried running select statments with a view?

the way I was writing back to our 400 was the following;

insert into LinkedServer.DB.Catalog.Tablename

the 400 is very picky about catalog, library etc..