"Test connection succeeded" but "An unexpected error occurred in the ODP.NET, Managed Driver"

Question

I want to connect Oracle from Visual Studio 2015 Enterprise Update 3. After installed ODT For Visual Studio 2015, to add new connection with data provider Oracle Database (ODP.NET Managed Driver) popup seems weird, test connection succeeds even with empty connection string -added ss- and when press ok, unexpected error occures.

64bit machine, uninstalled ODAC, repaired Visual studio 2015 and installed ODT for vs 2015.

Thanks and regards,

error

Answer 1

This is late but ... the problem in my case was, ODT had lost its connection to the TNSNAMES file, somehow, after the update. I was getting the exact same error: "An unexpected error occurred in the ODP.NET, Managed Driver" BUT, the "test connection" = success.

What I did was uninstall the ODT for VS and reinstall. Be sure to now run VS as Administrator. Next, I get a different error when I step on the connection: "xxxxx is not found in the available list of aliases". This is a clue to TNSNAMES.ora being wrong or not found.

In Visual Studio, going through Server Explorer -> Modify Connection, and now the more-expanded dialog box is back.

I clicked the "Search..." button (in the wizard) to locate the correct Tnsnames.ora, and then chose the Copy option to copy TNSNAMES.ora into the location that ODT wants: "c:\program files (x86)\oracle developer tools for vs2017\network\admin\". Here is where you must be Admin or the copy will fail.

At that point you can choose your data source name, and it should work. Hope this helps, it definitely fixed the problem for me. Your steps may vary, but the problem was the ODT had lost it's TNSNAMES copy after the update.

Answer 2

UPDATE 22/11/2019

Hi all,

After wasted hours (and maybe years too) trying to solve Oracle problems with Visual Studio, I discovered that if one has VS2017 and VS2019 installed and ODTforVSXXX is reinstalled, in my case ODTforVS2017_122011.exe, the installer adds all the settings in the file "devenv.exe.config" of the most current Visual Studio, in this case VS2019. To solve the error, you must copy these settings from "devenv.exe.config" (2019) to "devenv.exe.config" (2017).

<dependentAssembly>
    <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral" />
    <codeBase version="4.122.1.0" href="c:\program files (x86)\oracle developer tools for vs2017\odp.net\managed\common\oracle.manageddataaccess.dll" />
</dependentAssembly>
<dependentAssembly>
    <assemblyIdentity name="Oracle.ManagedDataAccess.EntityFramework" publicKeyToken="89b483f429c47342" culture="neutral" />
    <codeBase version="6.122.1.0" href="c:\program files (x86)\oracle developer tools for vs2017\odp.net\managed\common\ef6\oracle.manageddataaccess.entityframework.dll" />
</dependentAssembly>

<system.data>
      <DbProviderFactories>
         <remove invariant="Oracle.ManagedDataAccess.Client" />
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
        <remove invariant="Microsoft.SqlServerCe.Client" />
        <remove invariant="Microsoft.SqlServerCe.Client.3.5" />
        <remove invariant="Microsoft.SqlServerCe.Client.4.0" />
        <add name="Microsoft SQL Server Compact 4.0 Client Data Provider" invariant="Microsoft.SqlServerCe.Client.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact 4.0 Client" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
      </DbProviderFactories>
    </system.data>

I hope it serves something. Cheers!