SQL Server和Oracle之间的分布式事务

Question

我在启动SQL Server事务并尝试提交该事务时遇到了问题。

DECLARE @return_value int,
        @ERROR_MESSAGE nvarchar(2000)

BEGIN TRANSACTION
    EXEC @return_value = [dbo].[SEND_EMAIL]
            @SUBJECT = N'subject',
            @BODY = N'body',
            @RECEIVERS = N'[e-mail]',
            @ERROR_MESSAGE = @ERROR_MESSAGE OUTPUT

    SELECT @ERROR_MESSAGE AS N'@ERROR_MESSAGE'

    COMMIT TRANSACTION

    SELECT 'Return Value' = @return_value
GO

这是需要翻译的内容：

OLE DB提供程序“OraOLEDB.Oracle”为链接服务器“linked_server”返回消息“无法加入事务。”。

(1行受影响)

Msg 3930，级别16，状态1，第16行
当前事务无法提交，并且不能支持写入日志文件的操作。回滚事务。

(1行受影响)
Msg 3998，级别16，状态1，第3行
批处理结束时检测到未提交的事务。事务被回滚。

我在SQL Server中的存储过程如下：

BEGIN TRY
    EXECUTE('Call Schema.Package.StoredProcedure(?,?,?,?,?)', @subject, @body, @receivers, @vcSendBy, @ERROR_MESSAGE OUT) AT [linked_server]

END TRY
BEGIN CATCH

    SET @ERROR_MESSAGE = error_meessage();

END CATCH

这段代码可以不用 BEGIN TRANSACTION 和 COMMIT，但我不知道为什么。

提前感谢。

- csuazo

---

你为什么要使用事务来发送电子邮件？事务用于将数据保持一致的状态。发送电子邮件的操作不应该改变数据库状态。 - bastos.sergio

无法提交的事务是我在Jdeveloper 12c中使用CallableStatement尝试获取某些参数时遇到的相同问题，上面的代码是手动复现该问题的一种方式。 - csuazo

我通过在 Oracle 中执行存储过程来解决我的问题，在 SQL Server 的存储过程中，我调用了该函数。 - csuazo

Answer 1

我通过在函数内部执行Oracle中的存储过程来解决我的问题，在SQL Server中的存储过程中，我调用了这个函数。

 FUNCTION FUNCTION_CALL_SP (
           SUBJECT IN VARCHAR2,
           BODY IN CLOB,
           RECEIVERADDRESS IN varchar2,
           send_by IN varchar2
 ) RETURN varchar2 IS 

ERROR_MESSAGE   VARCHAR2(400);

BEGIN

SP_SEND_EMAIL(
    SUBJECT           =>   SUBJECT,
    BODY     =>   BODY,
    RECEIVERADDRESS  =>   RECEIVERADDRESS,
    send_by          =>   send_by,
    ERROR_MESSAGE   =>   ERROR_MESSAGE
);
return ERROR_MESSAGE;
END FUNCTION_CALL_SP ;

现在在我的SQL Server存储过程中，我有以下内容：

   SET @vQuery = 'SELECT @vfResult = A.ERRORMESSAGE FROM OPENQUERY(BCIE,''SELECT SCHEMA.PACKAGE.FUNCTION_SEND_EMAIL('''''+@SUBJECT+''''', '''''+@BODY+''''', '''''+@RECEIVERS+''''', '''''+@SEND_BY+''''') ERRORMESSAGE FROM DUAL'') A';

    BEGIN TRANSACTION
    EXEC SP_EXECUTESQL 
              @Query  = @vQuery
            , @Params = N'@vfResult NVARCHAR(MAX) OUTPUT'
            , @vfResult = @vfResul OUTPUT

    SET @ERROR_MESSAGE = @vfResult;
    COMMIT TRANSACTION

并且运行良好。

- csuazo

Answer 2

你需要使用分布式事务协调器。如果将您的代码置于事务中是一个要求，那么您的问题并没有简单的通用解决方案。

请查看此处的文档：MSDN 分布式事务 此外，这是另一个关于该主题的好链接：DTC

- Neo

Answer 3

因为您的事务涉及多个数据库，所以需要确保您使用了分布式事务。请参阅此处以了解如何配置您的服务器。

配置完服务器后，您可以使用以下语法启动分布式事务：

BEGIN DISTRIBUTED TRAN
    --INSERT, UPDATE, DELETE Data on SQL Server Table
    --INSERT, UPDATE, DELETE Data on Oracle Server Table
COMMIT TRAN 

- bastos.sergio