Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

TransactionScope hanging on second connection open

I am having trouble getting my TransactionScope to work correctly.  I need to access 2 different databases as a single transaction.  I can modify database one just fine, but when I try and open database two, it hangs.

I have enabled Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound and No Authentication Required for both my PC and the server where SQL Server is hosted.

This is a multi-threaded windows forms app.

I have posted a sample code snippet and where it is hanging.

My dev system is running Vista 64 and VS2008.  My SQL Environment is running on Windows Server 2008 with SQL Server 2008.

Thanks,
-Scott
public void TestTransactionScope()
{
  using (TransactionScope scope = new TransactionScope())
  {
    using (SqlConnection cn1 = new SqlConnection("Data Source=solitude;Database=DB1;User ID=CSDBUser;Password=PWD;Enlist=True;"))
    {
      using (SqlCommand cmd1 = new SqlCommand("Update Orders Set CustomerPO='NewValue' Where OrderID='SLGTest'", cn1))
      {
        cn1.Open();
        cmd1.ExecuteNonQuery();
      }
    }
 
    using (SqlConnection cn2 = new SqlConnection("Data Source=solitude;Database=DB2;User ID=CSDBUser;Password=PWD;Enlist=True;"))
    {
      using (SqlCommand cmd2 = new SqlCommand("Update PM40100 Set AgeBy=1", cn2))
      {        
        cn2.Open();  // <--  Hangs on this open
        cmd2.ExecuteNonQuery();
      }
    }
 
    scope.Complete();
  }
}

Open in new window

0
ScottieSLG
Asked:
ScottieSLG
  • 4
  • 3
2 Solutions
 
williamcampbellCommented:
If you remove the first db call does it still hang?


0
 
ScottieSLGAuthor Commented:
No, it doesn't hang on the second one if I try it independently.

I tried them independently, and I also tried reversing the order of the 2 SqlCommands and the first one works while the second one hangs.

It also appears that if I change the password to an invalid password in the connection string on the 2nd connection, it will give me an SqlException with an invalid password.
0
 
williamcampbellCommented:
Have you tried closing cn1 ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ScottieSLGAuthor Commented:
Tried closing cn1.  No change.
0
 
williamcampbellCommented:
Other options

Create Two Functions  One Call in Each

Remove the TransactionScope outer using block

Check the return values from ExecuteNonQuery  (maybe 0)

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

This link has a sample identical to yours...
0
 
ScottieSLGAuthor Commented:
I finally figured it out!  The firewall was blocking DTC.

On the server firewall settings, under Exceptions, I checked "Distributed Transaction Coordinator" and everything started working just fine.

Thanks for your help, William!
0
 
williamcampbellCommented:
Glad you figured it out
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now