During configuring replication on one of the server, I received following error. This is very common error and the solution of the same is even simpler.
MSDTC on server is unavailable. Changed database context to publisherdatabase. (Microsoft SQL Server, Error: 8501)
Solution:
Enable “Distributed Transaction Coordinator” in SQL Server.
Method 1:
- Click on Start–>Control Panel->Administrative Tools->Services
- Select the service “Distributed Transaction Coordinator”
- Right on the service and choose “Start”
Method 2:
- Type services.msc in the run command box
- Select “Services” manager; Hit Enter
- Select the service “Distributed Transaction Coordinator”
- Right on the service and choose “Start”
Reference : Pinal Dave (https://blog.sqlauthority.com)
24 Comments. Leave new
Thank you for sharing, also pinal have you setup merge replication, if so could you blog an article on it. Personally i have done transactional and snapshot replication.
Thanks for this article. It helps me a great. I got the same error for first time while configuring replication and confused but solved as per this articles.
Thanks you very much….
What happens if the Distributed Transaction Coordinator is already started when you get that error? Is it ever as easy as just start a server. Maybe in fairy tale land.
Thanks for sharing this, but when I started that service then I am getting this error
“Error Code: -2146232060 Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding” issue now.
My application was working perfectly earlier, don’t know whats wrong now. Please guide.
When ever we change the database in the drop down list available in microsft sql server management studio, we get ‘server is not available’. Does anyone know why we get such a message?
Check if the network is working correctly
System.Data.SqlClient.SqlException was unhandled by user code
Message=”Insert Error: Column name or number of supplied values does not match table definition.”
Source=”.Net SqlClient Data Provider”
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=213
Procedure=””
Server=”\\\\.\\pipe\\C4EF914B-A3EE-47\\tsql\\query”
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Ragistration.Button1_Click(Object sender, EventArgs e) in d:\HB_DVS\Ragistration.aspx.cs:line 40
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
i got this error when i insert data in table………
plz give me some solution….
DECLARE @srvname varchar(50) — Servername
create table #temp(name varchar(20),mode varchar(50))
create table #servtemp(servername nvarchar(50),RunDate date)
DECLARE @getsrvname CURSOR
SET @getsrvname = CURSOR FOR SELECT name FROM master.sys.servers where is_linked 0 — SET cursor statement
OPEN @getsrvname
FETCH NEXT
FROM @getsrvname INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #temp exec( ‘[‘+@srvname+ ‘ ].master.sys.xp_loginconfig [login mode]’)
insert into #servtemp values(@srvname)
exec(‘select * from #servtemp,#temp’)
FETCH NEXT
FROM @getsrvname INTO @srvname
END
–drop table #servtemp
–drop table #temp
CLOSE @getsrvname
DEALLOCATE @getsrvname
I am trying to run this for this I am getting error as :
Msg 8501, Level 16, State 3, Line 1
MSDTC on server ‘XYZ’ is unavailable.
I tried restarting service but still error persists….
I am Using SQL Server 2008R2.
If you’re running into this issue on a linked server, you’ll need to enable remote DTC requests on Component Services:
https://docs.microsoft.com/en-us/previous-versions/commerce-server/ms936442(v=cs.70)
Thank you it worked :)
Thanks it worked :D
Thanks..It worked!
It worked great!
hi : can you pls let me know why does a select query works fine using linked server but the sp fails when called through trigger? why should be enable msdtc? if it fails it has to fail for select as well correct? any light into this issue will help me. thanks in advance.
The trigger is automatically promoted to a distributed transaction. I was able to work around this by setting “Enable Promotion of Distributed Transactions” to False in the Server Options of my linked server.
Thanks! Very helpful.
Thanx 4 d post.Very helpful.
Hi Pinal,
Can we you msdtc through the internet without vpn. I used to msdtc with vpn but i couldnt use withouth vpn. What can i do for this, or is there any solution to start distrubuted transactions without vpn
Click on Linked Server Properties. Click Server Options. Change Enable Promotion of Distributed Transactions to FALSE.
OR
EXEC master.dbo.sp_serveroption @server=N’XXX’, @optname=N’remote proc transaction promotion’, @optvalue=N’FALSE’
GO
it worked for me..a million tnx!
many thanks, really help me :)
Thank you It helps me