I get this error in SQL Server 2005 Express:
unable to begin a distributed transaction
SW-05 is a linked server (2003 Server, SQL Server 2005 Standard) that is accessible when running queries in the query window. It's when the code is run within triggers and stored procedures that I get this error. Locally, I am running XP Pro with SQL Server 2005 Express.
Here is what I have set so far:
I have enabled the MSDTC service on both computers and set them to 'No authorization Required', logging in as NT AUTHORITY\NetworkService.
I have the following checked:
Network DTC Access, Allow Remote Clients, Allow Remote Administration, All Inbound, Allow Outbound, Enable Transaction Internet Protocol , Enable XA Transactions
I have added msdtc.exe to each side's firewall and also opened port 135 on both sides.
I have stopped and restarted the MSDTC service on each side. Yet I still get that error in my triggers.
OLE DB provider "SQLNCLI" for linked server "SW-05" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure sp_switchboard_insert, Line 56
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SW-05" was unable to begin a distributed transaction.