链接服务器INSERT，UPDATE和DELETE失败，并显示“未知提供程序错误". [英] Linked server INSERT, UPDATE and DELETE fails with "Unknown provider error"

问题描述

突然对于某些文件(我认为是AS/400的远程系统中的表)，INSERT，UPDATE和DELETE失败.

我们使用的链接服务器是在SQL Server中设置的，并且它使用的是ODBC数据源(DSN).数据源是用于Windows iSeries Access的ODBC数据源".

只有一个表存在此问题.我们可以使用同一台链接服务器在其他表中进行插入和更新，而不会出现任何错误，而SELECTs仍可用于有问题的表.

我们为INSERT和UPDATE语句获得了这些消息(在下面的代码中替换了服务器和数据库名称):

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.

DELETE会显示以下消息:

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.

如果您对此有任何线索，请随时回答此问题.

谢谢， 安德烈亚斯(Andreas)

解决方案

发生此错误的原因是已关闭AS400文件的日志记录，我们已使用链接服务器和ODBC iSeries数据源从SQL Server连接到AS400文件.这也关闭了承诺控制.

但是，在ODBC iSeries数据源上将提交设置为立即提交(* NONE)"无济于事. (也许除了更改该设置之外，还有更多其他功能.)

AS/400系统的数据库管理员使用其默认设置(包括日记和承诺控制)重新创建了表，然后该表又恢复了正常，并从链接的服务器连接中进行了插入，更新和删除. /p>

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

解决方案

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.

这篇关于链接服务器INSERT，UPDATE和DELETE失败，并显示“未知提供程序错误".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！