Distributed transaction between SQL Server and Oracle

Question

I am having trouble when I begin a transaction and attempt to commit the transaction that is initiated on 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'[email protected]',
            @ERROR_MESSAGE = @ERROR_MESSAGE OUTPUT

    SELECT @ERROR_MESSAGE AS N'@ERROR_MESSAGE'

    COMMIT TRANSACTION

    SELECT 'Return Value' = @return_value
GO

And return this:

OLE DB provider "OraOLEDB.Oracle" for linked server "linked_server" returned message "Unable to enlist in the transaction.".

(1 row(s) affected)

Msg 3930, Level 16, State 1, Line 16
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

(1 row(s) affected)
Msg 3998, Level 16, State 1, Line 3
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

My stored procedure in SQL Server is this :

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

This work without BEGIN TRANSACTION AND COMMIT but I don't know why.

Thanks in advance.

Answer 1

I solve my problem executing the stored procedure in Oracle inside a function, in my stored procedure in SQL Server I call the function

 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 ;

Now in my stored procedure in SQL Server I have this :

   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

And works fine.