PostgreSQL ODBC Not Fetching Data Despite Successful Connection

[steng]

Hi guys, I ran into a really rare situation that I was not able to find much resources online, so I want to see if anyone has some insights on it. 

I was trying to connect the input tool to a PostgreSQL ODBC. The environment is already set up and I get a prompt of “a successful connection was made and no errors were found parsing the query” when I tested the query. However, when running the flow, the input tool stays at 0% and would not fetch any data. I have tried to keep the flow running for a long time or set a record limit (even 1 record would not work), yet it is never able to pull anything into Alteryx. 

I also get a notice saying ODBC Driver version: 03.51 and a warning saying "Progress will not be displayed. Record count unavailable" when the flow stays at 0%.

Has anyone encountered similar issues? I was even able to load the data in Tableau but just not in Alteryx, where I need to

[patrick_mcauliffe]

Are you using a 32 bit driver or 64 bit driver?

[steng]

A 64-bit one!

[patrick_mcauliffe]

Have you tried the 32 bit driver?  I never could get the 64 bit driver to work, but for some reason the 32 bit worked for me.

[steng]

I just took a look, seems like my 32-bit driver does not have the PostgreSQL option to set the environment, so all I can use is still the 64-bit one...

Thanks for the note any way! Really perplexed by why this is  happening 

[patrick_mcauliffe]

I believe you have to completely uninstall the 64 bit first.  I'll dig up my notes in the morning and see how I did it.

[steng]

Thank you for the effort! Hope you have a great night

[patrick_mcauliffe]

Hey @steng I found my notes and an old virtual instance I had set up for this type of connection.

The 32 bit driver was just for writing bulk spatial.  Not the case you're looking for.

The 64 bit driver (ANSI version) had to be installed as admin under a system DSN.

Here are screenshots of the working driver config:

Then click Datasource:

And in Manage DSN:

[steng]

@patrick_mcauliffe  Thanks so much for the detailed notes! I have checked and made sure all of my ODBC setting are the same as yours, yet Alteryx was still not able to run through as desired. 

One quick question: when you click on "Connection Test" in page 3 of advanced options (picture 4), did you have a prompt suggested connection successful but also a MSDTC error asking you to enable XA transaction? I am wondering if this is what is blocking the request. 

[patrick_mcauliffe]

Hey @steng I did not get such a prompt.  I think you're on the right track to look into that.