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.

Why are you using a transaction to send email? Transactions are used to keep data in a consistent state. The action of sending an email should not alter the database state. — bastos.sergio
– bastos.sergio, Commented Nov 22, 2016 at 17:46
the uncommitable transaction is the same problem that I have in a callablestament in Jdeveloper 12c when I try to get some parameters, the code above is a way to reproduce the problem manually — csuazo
– csuazo, Commented Nov 22, 2016 at 19:35
I solve my problem executing the store procedure in Oracle inside a Function, in my store procedure in SQL Server I call the function — csuazo
– csuazo, Commented Nov 23, 2016 at 14:48

marc_s · Accepted Answer · 2016-12-17 22:08:55Z

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.

Community · Accepted Answer · 2017-04-13 12:42:39Z

0

You need to use the distributed transaction coordinator. There isn't a simple canned answer for your problem if having your code in a transaction is a requirement.

See the documentation here: MSDN Distributed Transactions

Here is another good link on the subject: DTC

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Nov 22, 2016 at 17:39

Neo

3,4478 gold badges37 silver badges46 bronze badges

Comments

bastos.sergio · Accepted Answer · 2016-11-23 10:27:02Z

0

Since your transaction spans multiple databases you will need to make sure you are using Distributed Transactions. See here on how to configure your server.

After the server is configured, you can start a distributed transaction with the following syntax:

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

answered Nov 23, 2016 at 10:27

bastos.sergio

6,7644 gold badges29 silver badges36 bronze badges

Collectives™ on Stack Overflow

Distributed transaction between SQL Server and Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related