Server 1
- Windows Server 2008(x86)
- SQL Server 2008 (x86) Standard Edition SP1
- Windows Server 2008 (x86)
- SQL Server 2008 (x86) Standard Edition SP1
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
declare @RetVal int,
exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0
COMMIT TRANSACTION
So far I have taken the following steps
1. NETWORK CONNECTIVITY
- Ping server1 from server2 -- success
- Ping server2 from server1 -- success
2. DTC CONFIGURATION
- Open Start->Administrative Tools->Component Services
- Expand the Component Services Node untill you find Local DTC right click and select properties option
- In the Local DTC properties click in the security section and set the configuration as shown in the image
Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically
The final step is make shure the DTC service is started and configured to start when windows start.
3. DTC SERVICE IS STARTED
- Open Start - > Open Start->Administrative Tools->Services
- Find the Distributed Transactions Coordinator Service
- Make sure the status is “Started” and the Startup Type is “Automatic”.
Follow the same step in Server2
After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1
OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True
All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...
No comments:
Post a Comment