Exception in npgsql 3.2.0 connecting to PostgreSQL via Excel PowerQuery

Question

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?

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
– Shay Rojansky, Commented Feb 3, 2017 at 20:15
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
– MarcelBeug, Commented Feb 4, 2017 at 6:55
@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
– soggers, Commented Feb 4, 2017 at 17:36
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
– Shay Rojansky, Commented Feb 4, 2017 at 20:12
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
– Shay Rojansky, Commented Feb 4, 2017 at 20:14

Carl Walsh · Accepted Answer · 2017-02-06 01:26:28Z

1

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.

edited Feb 6, 2017 at 1:26

answered Feb 6, 2017 at 1:14

Carl Walsh

7,2373 gold badges53 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

soggers Over a year ago

I downgraded to 3.1.9 and it now works. Looks like a problem with 3.2.0.

Collectives™ on Stack Overflow

Exception in npgsql 3.2.0 connecting to PostgreSQL via Excel PowerQuery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related