none
TransactionScope DTC error

    Question

  • I call save changes to two separate data models (CCWinEntities and CCWinEntities1) when the second models save changes method (context2.SaveChanges()) is invoked an error is thrown. The error states "The underlying provider failed on Open." The inner exception explains that the error occurred because MSDTC service has been disabled.

    Both data models use the same database and the same SQL connection string.

    I understand the error occurs (in SQL Server 2000 and 2005) when SQL transactions get promoted to a DTC Transaction due to SQL commands being execute in different connections to the database. (i.e. a SQL command is executed then the connection is closed then reopened then another command is executed again).

    However I am using SQL server 2008 and it should not promote the SQL transaction to a DTC transaction when the above scenario occurs. (correct me if I am wrong).

    I have proved that this scenario should work without running the DTC service by creating a new console app that uses the code below.

    Even though my production application executes code similar (a little bit more complex) to what is below it still throws "The underlying provider failed on Open." exception.

    Even though my Production application and this application execute similar code and use similar data models that are based on the same tables in the same database my production application is still throwing the error and I cannot work out why?

    Any help would be much appreciated.

    static void Main(string[] args)

            {

                using (TransactionScope tran = new TransactionScope())

                {

                    using (CCWinEntities context = new CCWinEntities())

                    {

                        Alert t = new Alert();

     

                        t.Active = true;

                        t.Address = "original address";

                        t.IsAlertOnItem = true;

                        context.AddObject("Alert", t);

                        context.SaveChanges();

                        context.Dispose();

                    }

                    using (CCWinEntities1 context2 = new CCWinEntities1())

                    {

                        AuditLog log = new AuditLog();

                        log.DateTimeOccured = DateTime.Now;

                        log.Screen = "this";

                        context2.AddObject("AuditLog", log);

      context2.SaveChanges();                      context2.Dispose();

                    }

                    tran.Complete();

                }

            }

    Thursday, August 06, 2009 8:14 AM

Answers

  • Solved It.

    The problem was that the database connection string the user id was the same but on was capitalised Eg User and one was in lower case Eg user. See example below.

    SQL Server 2008 must determine that its not the same connection if the connection string is not exactly the same including casing. Can anyone confirm this.



    <

     

    add name="AlertEntities" connectionString="metadata=res://*/AlertEntities.csdl|res://*/AlertEntities.ssdl|res://*/AlertEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=server;Initial Catalog=db;Persist Security Info=True;User ID=User;Password=password;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

    <

     

    add name="CCAdminEntities" connectionString="metadata=res://*/Admin.csdl|res://*/Admin.ssdl|res://*/Admin.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=server;Initial Catalog=db;Persist Security Info=True;User ID=user;Password=password;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

    • Marked as answer by Yichun_Feng Monday, August 10, 2009 5:05 AM
    Thursday, August 06, 2009 9:17 AM