CODEWITHSUNDEEP
t-sqltriggerslinked-servermsdtc
Cameron Castillo
Cameron Castillo

Reputation: 2832

Linked server can't start distributed transaction in trigger

I have a trigger that calls a stored proc. In the stored proc there's an INSERT statement on a Linked Server that gives me:

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyServer" was unable to begin a distributed transaction.

But when I replace the variable values in the sql string with the actual values and I run the exact same statement manually (i.e. not via the Trigger), it inserts the record.

DTC is enabled. Is my DTC settings wrong?

Query being executed:

INSERT INTO [MyServer].WorkForce.dbo.Faults (FaultID, CreatedOn, FaultStart, Reason, Description,  TaskID)
SELECT @NewFaultID, GETDATE(), T.CreatedOn, ISNULL(I.Reason, 'Unknown'), WFR.Description,
       T.TaskID
FROM Inserted I 
     INNER JOIN Tasks T ON I.TaskID = T.TaskID
     INNER JOIN FaultReasons WFR ON I.Reason = WFR.Reason
WHERE T.TaskID = I.TaskID

I'm using SQL Server 2012

Upvotes: 0

Views: 6907

Answers (2)

Hiram
Hiram

Reputation: 467

--fixed by : EXEC master.dbo.sp_serveroption @server=N'SVRLINK', @optname=N'remote proc transaction promotion', @optvalue=N'false'

Upvotes: 1

Cameron Castillo
Cameron Castillo

Reputation: 2832

I posted too quickly and found the answer, and yes the DTC settings were wrong. I enabled 'Allow Inbound', 'Allow Outbound' and 'No Authentication Required' and now it works.

Upvotes: 0

Related Questions