2

I have a C#, .Net 4.5.2 project, built in VS2012, which uses Entity Framework to connect to a SQL database. I now need to access a separate Oracle database, and have been trying to use SQL Anywhere 16 to make the connection. I know that the SQL Anywhere connection works because I have a test project which successfully uses it. The problem is that the connection.Open() method errors with this message:

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.

I suspect that Entity Framework doesn't like the additional connection, but enabling MSDTC is not an option as I only have FTP access to the server where the code will live. Is anyone able to suggest an alternative way to get this working, bearing in mind I know very little about Entity Framework?

Additional

There aren't any transactions set up as far as I can see (I only wrote the stuff for connecting to the Oracle database), nor is TransactionScope being used. Here's an edited version of my code:

var connectionString = new SAConnectionStringBuilder();
connectionString.Host = "***.***.***.***";
connectionString.DatabaseName = "*****";
connectionString.UserID = "*****";
connectionString.Password = "*****";
connectionString.ServerName = "*****";

using (SAConnection conn = new SAConnection(connectionString))
{
    using (SACommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
    }
}

Solution

I did some more searching, and after a bit of trial and error, managed to solve my problem. It seems the SQL Anywhere connection was automatically being put into a transaction. When this was then combined with the transactions being used by the entity framework connection, it was raised to a distributed transaction.

I have found that by putting the connection inside a transaction scope, and using the TransactionScopeOption.Suppress flag, it is no longer being put into a transaction, and therefore does not require MSDTC to be enabled. My code now looks like this:

var connectionString = new SAConnectionStringBuilder();
connectionString.Host = "***.***.***.***";
connectionString.DatabaseName = "*****";
connectionString.UserID = "*****";
connectionString.Password = "*****";
connectionString.ServerName = "*****";

using (TransactionScope scope1 = new TransactionScope(TransactionScope.Suppress))
{
    using (SAConnection conn = new SAConnection(connectionString))
    {
        using (SACommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
        }
    }

    scope1.Complete();
}

Thanks to everyone who responded

Colin Plater
  • 23
  • 5
  • 1
    This would only appear if you tried to execute both connections inside a transaction. That *is* a distributed transaction. Did you intend this or was it by accident? Please post the code you used. Are you using TransactionScope? – Panagiotis Kanavos Apr 15 '16 at 11:50
  • Is the test code where it is working connecting to the same database and performing the same action? It might help to get the simulation as close as possible. Also, since you have a test project working, what happens if you open an Entity Framework connection inside of your test project? I would try to get a very simple connection to both SQL Anywhere and Entity Framework working in the same test project, and step by step, make them match the code you are trying to write in production. – JMarsch Apr 15 '16 at 13:29
  • Can whoever voted to close please comment as to why? This seems like a reasonable question to me. – JMarsch Apr 15 '16 at 13:29

0 Answers0