Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 17 February 2016

ERROR: MSDTC on server 'ServerName' is unavailable.



Msg 8501, Level 16, State 2, Line 1

MSDTC on server 'Linked ServerName' is unavailable.

Solution:
 you have to restart services on SQL SERVER If not went correctly and throws above error


Kindly check Status of MSDTC.

RUN-->CMD

To open Command Prompt and Type sc query MSDTC

Kindly see below


 Here you can see the Sanple data on Table



 SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

--select top 5 * from linkedservername.dbname.dbo.tablename
--select top 5 * from [SQL2005].northwind.dbo.Customers--->select statement with linked server
update [SQL2005].northwind.dbo.Customers------->Update Statement with Linked Server
set [CompanyName] = 'Around the HoneyWell party'
where Customerid='AROUT'

COMMIT TRANSACTION
SET XACT_ABORT OFF


Kindly see it below


Kindly Uninstall from command Prompt from following
 So kindly check Services For MSDTC is not in the list due to uninstall 


You can confirm it with command prompt Querying  SC QUERY MSDTC
 


 After that Verification Kindly Install MSDTC


 After Verification Kindly Verify through Command Prompt Whether MSDTC is Running or not


Also Verify Services to Check MSDTC But it is not Started Services.


 Kindly Start the Services to Start it.


 Verify MSDTC is Started the Service

 After Run the T-SQL you got Error MSDTC on Server 'Linked ServerName' is UnAvailable

 If you Executed on SP also You Got Error MSDTC on Server 'Linked ServerName' is UnAvailable

 It is Bcos Destination Server Not Restarted/ReBooted on Services


Kindly  Restart Services on Destination Server

But Again You Got Error MSDTC on Server of  'SourceLinkedServerName' is UnAvailable

 But Still you can Get SELECT Statment from Linked Server Name but When you Issue Update Statement on Linked Server it throws Error
Kindly Restart/Reboot Services on Source Server




If you tried immediately then you will get above Error
 




To avoid this error kindly wait for few mints after restart MSSQL05 service then after try update statement.

  So you have to Restart Service Source then Wait for Few mints to Run T-SQL Statement


After Restarted Source Server and Wait for Few mints then Run below Scripts you can able to Execute without any issues

 Also you can able to Run through SP also see below