“交易界面"更新链接服务器上的表时出错 [英] "transaction interface" error when updating table on linked server

问题描述

我在尝试更新作为 SQL Server 数据库上链接服务器的 db2 数据库时遇到此错误.

I'm getting this error when trying to update a db2 database that is a linked server on our SQL Server db.

ERROR:root:('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]无法执行请求的操作，因为链接服务器iSeries"的 OLE DB 提供程序IBMDA400"执行不支持所需的事务接口.(7390) (SQLExecDirectW)')

ERROR:root:('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "iSeries" does not support the required transaction interface. (7390) (SQLExecDirectW)')

我正在通过 pyodbc 连接到 sql server，并且可以毫无问题地运行 sql 脚本.这是我用

I am connecting to sql server via pyodbc and can run sql scripts with no issues. Here is the sql I get the error with

sql3 = " exec ('UPDATE SVCEN2DEV.SRVMAST SET SVRMVD = ? WHERE svtype != ''*DCS-'' AND svcid = ? and svacct = ? ') AT [iSeries]"
db.execute(sql3, (row[2],srvid,row[0]))
db.commit()

以防万一这里是我使用 pyodbc 的连接字符串:

And just in case here is my connection string using pyodbc:

conn = pyodbc.connect("DRIVER={SQL Server};SERVER="+ Config_Main.dbServer +";DATABASE="+ Config_Main.encludeName +";UID="+ Config_Main.encludeUser +";PWD=" + Config_Main.encludePass)
db = conn.cursor()

另请注意，此查询在 SSMS 中运行良好.我也尝试过 openquery 方法，但没有运气.有什么想法吗?

Also note that this query runs just fine in SSMS. I have also tried the openquery method but had no luck. Any ideas?

推荐答案

Python 的 DBAPI 2.0 指定，默认情况下，连接应该在自动提交关闭"的情况下打开.这导致所有数据库操作都在必须在 Python 代码中显式提交(或回滚)的事务中执行.

Python's DB API 2.0 specifies that, by default, connections should open with autocommit "off". This results in all database operations being performed in a transaction that must be explicitly committed (or rolled back) in the Python code.

当带有 autocommit = False(默认值)的 pyodbc 连接向 SQL Server 发送 UPDATE 时，该 UPDATE 包含在由 SQL Server 管理的本地事务中.当 SQL Server 确定目标表在链接服务器上时，它会尝试将事务提升为由 MSDTC.如果用于管理链接服务器的连接技术不支持分布式事务，则操作将失败.

When a pyodbc connection with autocommit = False (the default) sends an UPDATE to the SQL Server, that UPDATE is enclosed in a Local Transaction managed by SQL Server. When the SQL Server determines that the target table is on a Linked Server it tries to promote the transaction to a Distributed Transaction managed by MSDTC. If the connection technology used to manage the Linked Server does not support Distributed Transactions then the operation will fail.

这个问题通常可以通过确保 pyodbc 连接启用自动提交来避免，或者通过

This issue can often be avoided by ensuring that the pyodbc connection has autocommit enabled, either by

cnxn = pyodbc.connect(conn_str, autocommit=True)

或

cnxn = pyodbc.connect(conn_str)
cnxn.autocommit = True

这将单独发送每个 SQL 语句，而不会包含在隐式事务中.

That will send each SQL statement individually, without being wrapped in an implicit transaction.

这篇关于“交易界面"更新链接服务器上的表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！