Distributed transaction error?

4
votes

In stored procedure I am using below statements. But it throwing Distributed transaction error when I ran Stored Proc.

Declare @res int
    Declare @mes as varchar(100)

    DECLARE  @Result TABLE (
result INT,
mesage VARCHAR(100))

    Insert @Result (result, mesage)         
            Exec [MySpeNet].[dbo].[GetMemberShipStatus]'3319994'
    select @res = result, @mes = mesage from @Result

Exception:

      Msg 7391, Level 16, State 2, Procedure GetMemberShipStatus, Line 19
    The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" 
for linked server "ASPQA" was unable to begin a distributed transaction.

OR

Is there any other way that I can store result and mesage without creating Temp table?

2
sqlsql-server-2005tsql
Is Microsoft Distributed Transaction Coordinator service running? – Nick Binnet

2 Answers

3
votes

Enable the options

  • Allow Remote Clients
  • Allow Outbound

On Security tab of Local DTC Properties in Component Services.

  • Go To Run, type comexp.msc.
  • Double click "Console Root".
  • Double click "Component Services.
  • Double click "Computers".
  • Double click "My Computer".
  • Double click "Distributed Transaction Coordinator".
  • Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties.
  • Click the "Security" tab.
  • Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound".
3
votes

This is what worked for me:

Inside SQL Server Management Studio, expand Server Objects, then Linked Server, then right click on the linked server in question and choose 'Properties.' Select the 'Server Options' page, and make sure 'Enable Promotion of Distributed Transactions' is set to 'False'

Or you can do it with T-SQL:

USE master;
EXEC sp_serveroption '<<your linked server name>>', 'remote proc transaction promotion', 'false';