MSDTC on server is unavailable.

Topics: User Forum
Jan 26, 2007 at 2:35 AM
When using SQL 2000 I get an error: MSDTC on server 'COMPUTER' is unavailable. Any suggestions?
Jan 26, 2007 at 2:39 AM
I should mention I am aware of the fact that if you use TransactionScope with SQL 2000 it tries to join DTC. So I assume when using the Windows Workflow functionality it tries to join a transaction. I was wondering if there is a workaround for using it without DTC?
Jan 26, 2007 at 3:54 PM
In SQL 2005 I believe SQL provider/engine can handle transactions spanning multiple databases without escalating it to the DTC, but in SQL 2000 I think this by default does use the DTC.

This is all a bit vague in my memory and I can't confirm it with something in writing, so I reserve the right to be wrong :-) Please shout up if I am.

My own blog post about this kind of thing is here though - from quite a while ago now.

So, this boils down to the question - if you put the Workflow Tracking data and Page Flow correlation data in the same database, does it eradicate your error message?

Jan 27, 2007 at 1:22 AM
So here is the short story on how to fix this (well, so far so good anyway). SQL 2000 and the way transactions works in WF promotes to DTC. The solution that seems to work is adding SharedConnectionWorkflowCommitWorkBatchService to the list of services for hosting and setting a common parameter for the connection string. This prevents from promoting to DTC.

So it would end up looking something like this in the web.config:

<hostingWorkflowRuntime Name="Hosting">

<add name="ConnectionString" value="Data Source=sql;Initial Catalog=database;Persist Security Info=True;User ID=username;password=password;"/>

<add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" LoadIntervalSeconds="5" UnloadOnIdle="true"/>
<add type="System.Workflow.Runtime.Hosting.SharedConnectionWorkflowCommitWorkBatchService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" LoadIntervalSeconds="5" UnloadOnIdle="true"/>
<add type="System.Workflow.Runtime.Hosting.ManualWorkflowSchedulerService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" useActiveTimers="true"/>
<add type="System.Workflow.Activities.ExternalDataExchangeService, System.Workflow.Activities, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>

Jan 27, 2007 at 5:09 AM
Can someone from verify from p&p if I am doing things right? This method seems kind of slow in the beginning but then works faster. So maybe loading assemblies the first time? Any guidance would be helpful.
Jan 29, 2007 at 8:36 PM
SQL 2000 was not one of the platforms we targeted in development or testing. This was a decision made not to alienate folks on the older platform, but to reduce our test matrix drastically. (We wanted to ship sooner rather than later).

The fix you posted looks like a good idea at first glance. If it works for you and scales enough for your situation, do it. If not, ping this forum again. We can then dive deep into the WorkFlow on Sql2000 docs and look at other options.