Exception in npgsql 3.2.0 connecting to PostgreSQL via Excel PowerQuery

0
votes

I have a PostgreSQL 9.6.1 database from which I want to extract data for use in Excel with Get & Transform (PowerQuery). I have 64-bit Excel 2016 Pro Plus running on Windows 10 64-bit with .NET framework 4.6 installed.

When I try to use "Data->New Query -> From Database -> From PostgreSQL Database", I put the server name (localhost) and DB name into the dialog box.

I then get the error message,

An error happened while reading data from the provider: 'The type initializer for 'Npgsql.Counters' threw an exception.'

I have the following setup:

  • npgsql 3.2.0 installed with nuget. There are 3 versions of the DLL, net45, net451 and netstandard1.3. I used net451 for no other reason that it was closest to 4.6.
  • mono.security.dll 4.0.0.0. I added both of the DLLs to the global cache with gacutil.exe

  • I updated machine.config as follows for DbProviderFactories

    add description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" name="Npgsql Data Provider" invariant="Npgsql" support="FF"

Could anyone help me understand if I did anything wrong?

1
excelpostgresqlnpgsqlpowerquery
Can you post a full exception stack trace with details? The message you posted provides very little info. You also don't need Mono.Security.dll in your GAC (or anywhere else), it's recommended you remove it. - Shay Rojansky
Although I know nothing about the subject, I can point you to another post about Power Query and PostgreSQ over here: social.technet.microsoft.com/Forums/en-US/… - MarcelBeug
@ShayRojansky the official Microsoft line is that you need to install mono.security link. I removed the Mono.Security, but still have the problem. I don't have the stack trace, only the dialog box. Where can I get the stack trace? - soggers
That article is extremely out of date - Npgsql hasn't depended on Mono.Security since version 2.x (years back)... You can also use the Npgsql MSI installer to do GAC and machines.config installation rather than doing everything manually as described. - Shay Rojansky
Regarding where to get more info, I'm sure Excel produces logs somewhere, but I don't know much about it... Can you please open an issue on the Npgsql github repo, I may have time to dig in. - Shay Rojansky

1 Answers

1
votes

It looks like this is an issue with a recent npgsql release? Until that's fixed, if you install version 3.1 or 3.0 Power Query should work :)

One other thing you could try is using net45. We officially support .NET 4.5.1 but sometimes you have more luck with older releases.