无法参与交易 [英] Unable to enlist in the transaction

问题描述

我有问题:我已经在 Windows 中设置了 1 个 SQL Server 2014 Express，在另一台服务器中的 Linux 中设置了 1 个 Oracle 数据库服务器 11g.

I have problem: I have 1 sql server already setup SQL Server 2014 Express in Windows and 1 Oracle Database server 11g in Linux in another server.

现在我想通过链接服务器将数据从 SQL 服务器插入到 Oracle 数据库中.我能够成功地进行 SELECT、UPDATE、INSERT.但是，当我尝试执行具有事务的存储过程时，我收到以下错误:链接服务器UGOV"的 OLE DB 提供程序OraOLEDB.Oracle"返回消息无法在事务中登记.".强>

Now i want to insert data from SQL server to Oracle database through link server. I am able to make SELECT, UPDATE, INSERT successfully. But when I try to execute a stored procedure that has a transaction I get the following error: OLE DB provider "OraOLEDB.Oracle" for linked server "UGOV" returned message "Unable to enlist in the transaction.".

程序的结构是这样的:

USE [payroll]
GO
/****** Object:  StoredProcedure [dbo].[InsertPayrollUGOVDocumentsHead]    Script Date: 12/1/2016 4:27:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC master.dbo.sp_serveroption @server='UGOV', @optname = 'remote proc transaction promotion', @optvalue = 'false' 
--GO 

ALTER PROCEDURE [dbo].[InsertPayrollUGOVDocumentsHead]
(
@ID_KARAKTERISTIKE INT,
@MUAJI INT,
@VITI INT,
@USER_ID VARCHAR(50)
)
 AS 
BEGIN
BEGIN  TRY
 BEGIN DISTRIBUTED TRANSACTION insertDocumentsHead

INSERT INTO........
...
....

 COMMIT TRANSACTION insertDocumentsHead
     PRINT 'Transaction success'

END TRY
BEGIN CATCH
     ROLLBACK TRANSACTION insertDocumentsHead
     PRINT 'Transaction wrong'
END CATCH
END

如果我在没有事务的情况下执行查询就可以了.当我使用事务时，出现错误.

If I execute the query without a transaction it is ok. When I use transactions I get the error.

这个想法是从我网站上的一个按钮调用这个过程并将数据插入到 oracle DB.

The idea is to call this procedure from a button in my website and insert data to the oracle DB.

我做过的事情是:

1. 启用 MSDTC 服务(分布式事务协调器已启动).
2. 从控制面板启用网络 DTC 访问、组件服务以允许入站"等.
3. 我的OracleMTSRecoveryService"正在我的服务列表中运行.

请帮我解决这个问题.我真的很感激.非常感谢.

Please help me to solve this problem. I would really appreciate it. Thank you very much.

安迪

推荐答案

在您的链接服务器中，转到服务器选项并将 启用分布式事务 RPC 的提升 设置为 False

In your Linked Server got to server options and set Enable promotion of Distributed Transaction RPC to False

这篇关于无法参与交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！