CODEWITHSUNDEEP
sqlsql-serveroracle-databasestored-procedureslinked-server
csuazo
csuazo

Reputation: 164

Distributed transaction between SQL Server and Oracle

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.

Upvotes: 3

Views: 4721

Answers (3)

csuazo
csuazo

Reputation: 164

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.

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

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

Upvotes: 0

Neo
Neo

Reputation: 3409

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

Upvotes: 0

Related Questions