We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

TransactionScope hanging on second connection open

Medium Priority
1,056 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

If you remove the first db call does it still hang?


Author

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.
Have you tried closing cn1 ?

Author

Commented:
Tried closing cn1.  No change.
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...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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!
Glad you figured it out
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.