Linked server trigger error

Question

hi,

I am passing one trigger from A server to B server,  this is the trigger running on A server on LeaveDetails tble

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[upload_leave] 
   ON   [dbo].[LeaveDetails]
   AFTER update
AS 

BEGIN
SET NOCOUNT ON;
insert into [Bserver-SQLDB-03].[BPayroll].dbo.LeaveTbl([ID],[CPR],[DateFrom],[DateTo],[Notes])
select inserted.ID, EmployeeDetails.CPR,inserted.FromDate,inserted.ToDate,''
FROM            EmployeeDetails INNER JOIN
                         inserted ON EmployeeDetails.ID = inserted.EmployeeID 
                         where inserted.LeaveStatus=9 and ResumeForm=0

When front end the application call the trigger then this below error show. I tried in many ways to fix this issue but the error remain same. I found so many solution in google search as well on this Microsoft forum but still the issue remain same. Is any expert can help me.???? pls

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "Bserver-SQLDB-03" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "Bserver-SQLDB-03" returned message "No transaction is active.".

---

Afzalkhan

Answer 1

You must enable DTC service but better is to remove this command from the trigger  and use instead a stored procedure

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Hi Sir,

How can I use in store procedure? from front end application when I click on Approve button then this error come.

Thank you.

---

Afzalkhan

Answer 3

A Trigger is nothing else then a Stored Procedure, which fires on DML; there won't be any differences and you will get the same error.

You must setup MS DTC first; see Recommended MSDTC settings for using Distributed Transactions in SQL Server

BTW, not a good idea to access a linked server in a trigger/transaction. If the linked server isn't available, it will break your OLTP system.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Are not you able to change the application source?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 5

Refer similar error below on different situations:

http://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction

http://stackoverflow.com/questions/24014718/the-operation-could-not-be-performed-because-ole-db-provider-sqlncli11-for-lin

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45176b24-26f9-4ffc-bfdf-cb97f4737958/distributed-transactions-not-working?forum=sqldatabaseengine

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5765d77-9d79-43a4-93ec-b0a442005e2b/unable-to-access-the-linked-server-data-within-the-trigger-and-stored-procedure?forum=sqldatabaseengine

Answer 6

hi 

i have tried all this but still i am getting the same error.

Do you have any other solution?

Thank you.

---

Afzalkhan

Answer 7

By the way, have you tried Olaf suggestion?

Answer 8

AfzalkhanCheck the name of computer, I had the same error becuase I had 2 PC with same name.

---

Mario M