Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 23rd, 2006, 10:48 PM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Issues Using SQL server 2005

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;
         }
      }
"

  #2 (permalink)  
Old August 23rd, 2006, 10:59 PM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old August 23rd, 2006, 11:07 PM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

  #4 (permalink)  
Old August 24th, 2006, 03:53 PM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 99
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.

  #5 (permalink)  
Old October 17th, 2006, 08:09 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?



  #6 (permalink)  
Old October 18th, 2006, 07:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old October 18th, 2006, 10:12 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #8 (permalink)  
Old October 19th, 2006, 09:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

  #9 (permalink)  
Old October 19th, 2006, 09:14 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #10 (permalink)  
Old October 22nd, 2006, 07:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
reg conn to sql server 2005 from vb.net 2005.. veda SQL Server 2005 2 July 1st, 2008 12:16 AM
Issues moving to SQL 2005 Server iPagan BOOK: Accessible XHTML and CSS Web Sites: Problem Design Solution 1 January 17th, 2007 01:35 PM
SQL Server 2005/ VS 2003 Issues johnalp VS.NET 2002/2003 1 August 3rd, 2006 02:44 AM
SQL Server 2005/ VS 2003 Issues johnalp ADO.NET 0 July 10th, 2006 12:46 PM



All times are GMT -4. The time now is 09:37 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.