SQL SERVER – Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase

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:

  1. Click on Start–>Control Panel->Administrative Tools->Services
  2. Select the service “Distributed Transaction Coordinator”
  3. Right on the service and choose “Start”

Method 2:

  1. Type services.msc in the run command box
  2. Select “Services” manager; Hit Enter
  3. Select the service “Distributed Transaction Coordinator”
  4. Right on the service and choose “Start”

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase

  1. 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.

  2. 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….

  3. 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.

  4. 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.

  5. 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?

  6. 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….

  7. 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.

  8. 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.

  9. 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

  10. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s