none
Transaction Scope in SQL with same Connection String Multiple times showing MSDTC Service not configured for the second method

    Question

  • Hi,

    Am Using Transaction Scope to Insert to a Master Table and two Sub tables using same connection string.

    Am opening and closing connection string each time.

    Method 1. Insert into Master Table

    Method 2. Insert into Sub Table 1 with Master Table Primary Key

    Method 3. Insert into Sub Table 2 with Master Table Primary Key

    Connection string using : "server=IPAddress; database=DBName; uid=*;pwd=123; Pooling=True;Min Pool Size=5;Max Pool Size=2000;Connect Timeout=60;"

    My First Method executing within Transaction Scope But When Executing Second Method it shows 

    "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool"

    as am already set MSDTC Settings in my server

    MSDTC Settings in Component Services

    Please help me to solve this issue.

    Thanks in Adance

    Shemeer Oveli


    Wednesday, March 05, 2014 12:41 PM

All replies

  • What version of .NET are you using?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 05, 2014 3:44 PM
  • .NET Framework 3.5 and  SQL Server 2005
    Thursday, March 06, 2014 4:32 AM
  • Hello,

    Did you connect to the SQL Server remotely? If so, please try to enable MSDTC on both SQL Server database server and application server. You need to reboot the server in order to make the configuration work.

    You can test the MSDTC  on server by using following tool.
    DTCTester: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293799

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, March 06, 2014 9:03 AM
    Moderator
  • Am getting the Transaction Scope working well in SQL Server 2012.

    Wednesday, March 12, 2014 7:01 AM
  • Yep.  There were changes in the client and in SQL Server that allow multiple uses of the same connection string in the same TransactionScope to reuse a single connection, and not require DTC escalation.  IIRC you need .NET 3.5 or later and SQL 2008 R2 or later.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 12, 2014 1:46 PM