Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3261
  • Last Modified:

the operation could not be performed because OLE DB provider "SQLNCLI10" for linked server was unable to begin a distributed transaction

Hi guys,

Any ideas how to stop the above error? Did a little reading around and tried executing the below line on Server A, Server B being the linked server. This happens when a trigger is firing.

EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

I should add that the trigger with this code fails:
USE [TestingArea]
GO
/****** Object:  Trigger [dbo].[UpdateAscentLinkedServer]    Script Date: 10/23/2014 11:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UpdateAscentLinkedServer]
ON [dbo].[SysproBase]
AFTER UPDATE
AS
UPDATE [ASCENTLINK].[AscentXAF_Live].[dbo].[AscentCheckTable] SET AscentActive = 'False' WHERE AscentName = 'Joss'    

Open in new window


However if I manually run this line of code then it works fine:

UPDATE [ASCENTLINK].[AscentXAF_Live].[dbo].[AscentCheckTable] SET AscentActive = 'False' WHERE AscentName = 'Joss'  

Thanks,
Dean
0
deanlee17
Asked:
deanlee17
  • 6
  • 6
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How's your MSDTC configured?
0
 
deanlee17Author Commented:
Hello Vitor,

Where do I check this?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In Component Services, expand Computers / My Computer / Distributed Transaction Coordinator and then click Local DTC.
Network DTC Access should be Enabled. If not, enable it and test again.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
deanlee17Author Commented:
Ok ive located local DTC. I cant see Network DTC Access?

printscreen
0
 
deanlee17Author Commented:
Its ok I found it in properties
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. Sorry, should be right-click and not only click.
0
 
deanlee17Author Commented:
Does this need to be done on both servers? ive done it on the local one and no change so far, will this need to be done on the one thats being linked to as well?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, better to do it on both servers since it's a distributed transaction.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And you might need to enable the following options in the Security tab:
- Allow Inbound and Allow Outbound
0
 
deanlee17Author Commented:
Perfect, thanks.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are welcome.
When you are running manually the update, isn't a distributed transaction but a remote transaction and that's why it works. A distributed transaction means that you are making changes in the local server and also in remote server and that's why you need to configure MSDTC to allow that.
0
 
deanlee17Author Commented:
Ok perfect, thank you.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now