I've spent close to 2 days looking for an answer and only got a headache. We have our IBM DB2 linked server defined as IBM DB2 UDB for iSeries OLE DB Provider. We can run select, update and deletes we just cannot do an insert. Here are our sample statements.
SELECT * FROM OPENQUERY(OUR_DB2,'SELECT * FROM MYLIB.AUMP')
UPDATE OPENQUERY(OUR_DB2, 'SELECT * FROM MYLIB.AUMP WHERE AUPGM = ''MYPGM'' ') SET AUUSR = 'AUUSR_3'
DELETE OPENQUERY(OUR_DB2,'SELECT * FROM MYLIB.AUMP') WHERE AUPGM = 'MYPGM'
insert into OPENQUERY(OUR_DB2, 'select AUPGM,AUUSR from MYLIB.AUMP ') values('MYPGM' , 'MYUSER')
We have the provider "IBMDA400" set to allow in process. This was the only IBM provider on the drop down list to choose from. It's a SQL Server 2000.
When we try and run the insert this is the error data we get.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method.
I've Googled and tried a lot of suggestions out there but some see so involved or did not work. I don't see why everything but the insert works.
Any ideas ???