none
Power Query Update breaks connection to PostgreSQL database RRS feed

  • Question

  • After the update to Power Query 2.11.3625.144, we can't connect our PostgreSQL database anymore. 

    Error message is: 

    DataSource.NotFound: PostgreSQL: Unable to find a database provider with invariant name 'Npgsql'.
    This error may have been the result of provider-specific client software being required but missing on this computer.  To download client software for this provider, visit the following site and choose at minimum 'Npgsql version 2.0.12': http://go.microsoft.com/fwlink/?LinkID=282716

    Readding the Npgsql driver doesn't solve the issue. Has the version requirement changed with the Power Query update? Or is PostgreSQL connectivity completely broken?

    Thursday, May 8, 2014 2:02 PM

Answers

  • As of version 2.11, Power Query prefers to run under .NET 4.0 if you have it installed. So if the PostgreSQL driver is registered in the .NET 2.0 machine config but not the .NET 4.0 machine config, we might not be able to find it any more. If so, the solution is to add the PostgreSQL driver to the .NET 4.0 machine config. I can provide more information about doing that if needed.

    • Proposed as answer by Curt Hagenlocher Thursday, May 8, 2014 3:30 PM
    • Marked as answer by bsjut Thursday, May 8, 2014 3:50 PM
    Thursday, May 8, 2014 3:22 PM
  • The DbProviderFactories element was previously empty, and the "add" needs to be inside it. So the resulting structure should look like

    <system.data>
      <DbProviderFactories>
        <add ... />
      </DbProviderFactories>
    </system.data>

    • Marked as answer by bsjut Thursday, May 8, 2014 3:49 PM
    Thursday, May 8, 2014 3:46 PM
  • Thanks for pointing that out! Yes, other than picking the v4.0.30319 directory instead of the v2.0.50727 directory, everything else should be the same.
    • Marked as answer by bsjut Thursday, May 8, 2014 3:50 PM
    Thursday, May 8, 2014 3:28 PM

All replies

  • As of version 2.11, Power Query prefers to run under .NET 4.0 if you have it installed. So if the PostgreSQL driver is registered in the .NET 2.0 machine config but not the .NET 4.0 machine config, we might not be able to find it any more. If so, the solution is to add the PostgreSQL driver to the .NET 4.0 machine config. I can provide more information about doing that if needed.

    • Proposed as answer by Curt Hagenlocher Thursday, May 8, 2014 3:30 PM
    • Marked as answer by bsjut Thursday, May 8, 2014 3:50 PM
    Thursday, May 8, 2014 3:22 PM
  • Curt, 

    thanks for the fast reply. Could you provide more info? 

    And you guys should update http://office.microsoft.com/en-001/excel-help/connect-to-a-postgresql-database-HA104028095.aspx in this case, because it still leads users to install in teh .NET 2.0 environment. 

    Is the process other than that the same? 

    Thursday, May 8, 2014 3:25 PM
  • Thanks for pointing that out! Yes, other than picking the v4.0.30319 directory instead of the v2.0.50727 directory, everything else should be the same.
    • Marked as answer by bsjut Thursday, May 8, 2014 3:50 PM
    Thursday, May 8, 2014 3:28 PM
  • Hmm, 

    If I add the line, I get the following error message in Power Query: 

    'Npgsql' returned the error: 'Unrecognized element. (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config line 169)'.
    This error may have been the result of provider-specific client software being required but missing on this computer.  To download client software for this provider, visit the following site and choose at minimum 'Npgsql version 2.0.12': http://go.microsoft.com/fwlink/?LinkID=282716

    The code in the machine config looks like this: 

      <system.data>
            <DbProviderFactories />
            <add description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" name="Npgsql Data Provider" invariant="Npgsql" support="FF"/>
        </system.data>

    The node seems to be empty by default. Can it be that the surrounding stuff has to be differently written in that case?

    Thursday, May 8, 2014 3:35 PM
  • The DbProviderFactories element was previously empty, and the "add" needs to be inside it. So the resulting structure should look like

    <system.data>
      <DbProviderFactories>
        <add ... />
      </DbProviderFactories>
    </system.data>

    • Marked as answer by bsjut Thursday, May 8, 2014 3:49 PM
    Thursday, May 8, 2014 3:46 PM
  • Excellent, Curt! Thanks - this worked. 

    Maybe you guys can pass this on to also be reflected in the help article at 

    http://office.microsoft.com/en-001/excel-help/connect-to-a-postgresql-database-HA104028095.aspx

    Otherwise I'm sure a lot of people would run into the same wall as me :) 

    Thursday, May 8, 2014 3:49 PM
  • Yes, I've already forwarded that request to the appropriate people. Thanks again for pointing me to it!
    Thursday, May 8, 2014 3:54 PM
  • Seems like more than just PostgreSQL is broken.  I haven't been able to run web queries since installing the update either.
    Monday, May 12, 2014 2:56 PM
  • Please start a new thread for this "web queries" problem and provide a specific sample of something that doesn't work. Thanks!
    Monday, May 12, 2014 2:58 PM