When we run a distributed transaction against an instance of SQL Server, we may receive an error message. This problem can be resolved by following the below statements:
- Make sure that the MSDTC (Microsoft Distributed Transaction Coordinator) is enabled on both the servers.
- Go to Start –> Run –> Type as ‘Services.msc
- ~Right click on the above selected service and click on start. This starts the MSDTC service in the server (do the same on the remote servers also)
- Once the Service is started, the next step is to enable the Network Transaction.
- Go to Start –> Run –> Type as ‘dcomcnfg.exe’ or Goto Control Panel –> Administrative Tools –> Component Services
- In windows 7, Expand Console Root –> Compoent Services –> Computers –> My Computer –> Distributed Transaction Coordinator –> Local DTC –> Right Click Properties
- In windows XP, Expand Console Root –> Compoent Services –> Computers –> My Computer –> Right Click Properties
- Goto ‘Security’ Tab and check the checkbox ‘Network DTC Access’
- To allow the distributed transaction to run on the current server from a remote server, select the ‘Allow Inbound’ check box.
- To allow the distributed transaction to run on a remote server from the current server, select the ‘Allow Outbound’ check box.
- Under the Transaction Manager Communication group, select the ‘No Authentication Required’ option.
- Click on OK in Security Dialog Box
- If the settings are not affected once restart the server where the changes were done
We can check the status of service state by executing the below command in the query window (this need the corresponding permissions for executing):
EXEC xp_servicecontrol N'querystate',N'msdtc'