Linked server INSERT, UPDATE and DELETE fails with

Question

Suddenly INSERT, UPDATE and DELETE fails for a certain file (a table in a remote system which I believe is an AS/400).

The linked server that we make use of is set up in SQL Server, and it's using an ODBC data source (DSN). The Data source is an "ODBC-data source for iSeries Access for Windows".

Only one single table has this problem. We can make inserts and updates in other tables using the same linked server, without any errors, and SELECTs still work for the problematic table.

We get these messages for INSERT and UPDATE statements (server and DB names replaced in the code below):

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSERVER" reported an error. 
The provider did not give any information about the error.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSERVER" could not INSERT INTO 
table "[MYSERVER].[MYDB].[DMPCOM].[DMPXIF]". Unknown provider error.

And DELETE gives this message:

The OLE DB provider "MSDASQL" for linked server "MYSERVER" could not delete from 
table ""MYDB"."DMPCOM"."DMPXIF"". There was a recoverable, provider-specific 
error, such as an RPC failure.

If you have any clues to this, please don't hesitate to answer this question.

Thanks, Andreas

Answer 1

The reason to the error was that journalling had been turned off on the AS400 file, that we connect to from SQL Server using linked server and an ODBC iSeries datasource. This had also turned commitment control off.

Setting commit to "Commit immediate (*NONE)" on the ODBC iSeries datasource did however not help. (Perhaps there is more to it than changing that setting.)

The database administrator of the AS/400 system recreated the table with its default settings, including journaling and commitment control, and then it was all back to normal, and insert, update and delete worked, from the linked server connection.