CODEWITHSUNDEEP
sql-serveroracle-databaseoracle11gtransactionslinked-server
Andi
Andi

Reputation: 51

Unable to enlist in the transaction

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.

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.".

The structure of the procedure is this:

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.

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

The things I have done are:

  1. Enabled the MSDTC services (Distributed Transaction Coordinator is started ).
  2. Enabled the Network DTC access from the control panel, component services to 'Allow InBound' etc.
  3. My 'OracleMTSRecoveryService' is running in my services list.

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

Andi

Upvotes: 5

Views: 4853

Answers (1)

Saif quraishy
Saif quraishy

Reputation: 21

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

Upvotes: 2

Related Questions