I am just setting up the c# project files according to the instructions. I am choosing to use SQL2005 instead of the express version, so have updated the web.config and followed the instructions to set up my databases.

When I run the project in Visual Studio, the site launches correctly. I then proceed to add items to the shopping basket, and register a test user. However, when I try to purchase the item, I get the following error:

"MSDTC on server 'myServer' is unavailable."


This is thrown at in the MB.TheBeerHouse.DAL.SqlClient.SQLStoreProvider class, when running this code:

"public override int InsertOrderItem(OrderItemDetails orderItem)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("tbh_Store_InsertOrderItem", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = orderItem.AddedDate;
            cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = orderItem.AddedBy;
            cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = orderItem.OrderID;
            cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = orderItem.ProductID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = orderItem.Title;
            cmd.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = orderItem.SKU;
            cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = orderItem.UnitPrice;
            cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = orderItem.Quantity;
            cmd.Parameters.Add("@OrderItemID", SqlDbType.Int).Direction = ParameterDirection.Output;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (int)cmd.Parameters["@OrderItemID"].Value;
         }
      }
"

the MS DTC service was set to manual and was stopped. When I started it the web site worked.

Should this service be set to automatic?

Thanks,
JJ

I understand that the service is normally set to manual, as mine is.

So I still don't know why I am getting this error, or why I have to manually run MSDTC to overcome the error?

Because transaction processing is done through SQL Server, the transaction coordinator service must be turned on. The choice is yours about turning it to automatic, but unless you plan to use transaction processing there is really no need to have it running all the time. Just run the service as needed for testing.

Hi, I was able to go around this by adding Enlist=false to the connection string.

Notes:
1- I'm a novice, so I don't know what I'm doing :D
2- I think this will take the actions of the sql statement to be executed out of the scope of the transaction, correct?

I always leave my DTC on manual and it works fine without that change to the connection string. I don't remember seeing anything in this book that really uses a distributed transaction, but maybe my memory is fading (just ask my wife about that!).

You've got something else going on, and I'm not sure what. Somehow your system thinks you have an open distributed transaction, so it's trying to auto enlist your SQL 2005 transaction into that pending transaction. This is probably not a real transaction, of course. The action of SQL Server 2005 trying to auto-enlist you is is related to the new changes surfaced in the new System.Transaction namespace in ADO.NET 2.0. Microsoft beefed up it's ability to define and manage distributed transactions.

I've never been a fan of MS's DTC approach, but I concede that it might be useful in some cases. I just strongly dislike the "black box" approach to handling these critical transactions that can span tiers, machines, and networks. MS gave us very little ability to manage these transactions, although that is improved somewhat in 2.0. I still hear Bill Gates whisper "trust me" whenever someone talks about opening a DTC transaction, and Bill hasn't done enough to earn my trust in that regard (I'm still sore at Bill over the whole DCOM fiasco, which is intimately tied to DTC). But again, any capability is better than none, so I can't complain too much.

The first thing I'd check is your COM+ Component Services configuration. Look for anything odd there, and look to see if anything is in the active state when it normally shouldn't be. Most Distributed Transactions originate in COM+.

It might be as simple as a bad config file somewhere, perhaps not tied to COM+. Is there anything odd in your web.config or machine.config?

But your work-around of changing the connection string is safe and won't cause a problem unless you really need to use auto-enlistment with a distributed transaction someday (unlikely - especially on a developer's computer).

Eric

Hi, in my case, it happens both on my local machine, and on a hosted environment. I really have no control in checking anything on the hosted site.

Thanks for the explanation.

In that case it is likely a problem in one of your own web.config files. The hosting companies usually keep tight control of their own configuration. Do your have more than 1 of these? Did you recently compare it item-per-item with the one in the code download?

I'm pretty sure. I downloaded, unziped, created the DB, ran the code, did a website copy into a new directory at my hosting company. The only thing I touched in web.config is the connection string as far as I remember.

Did you change the SQL? There has to be some change you made that causes this behavior. Hundereds of people have used this code without similar transaction problems.

How did you deploy the DB to the hosting company?