PostgreSQL for Tableau metadata working in Designer but not on Server

Question

Hello,

I am coming across a weird issue. I am trying to schedule the output that comes from a PostgreSQL query that pulls Tableau's Workgroups metadata into Alteryx. The query is very large and was found in the TS_Data_Connections workbook here: https://community.tableau.com/docs/DOC-9974

Alteryx is telling me "Invalid SELECT statement. Unexpected token "Distinct" at line 1, pos 26060" but I am running the identical query in designer and it works fine so I don't think it could be true SQL code issue unless the server's syntax could be different but that wouldn't make any sense. I have run a simple Postgres query on the Alteryx server so I know it's not a connection issue so I'm truly stumped. I'll attach the query.

Anything could help thanks!

TS_data_connections_query.txt

CristianoJ · Answer

Please send an email to support@alteryx.com.

Include your phone number.  Mentioned it is about case 334815.

Also include the workflow and the following screenshots:

- designer version

- server's designer version

- screenshot of the error

- ODBC driver version on designer

- ODBC driver version on server's designer

patrickmckenna88 · Answer

Yes I should have clarified that I am testing in designer, just in the server's VM instead of my desktop. I had the same Postgres driver installed that I use on my desktop as well and am able to connect to tables in that designer but for some reason the SQL that I use on my desktop is having trouble on the server. I would recreate the SQL piece by piece in the server's designer, but it is very lengthy and complex as you can see from the txt file.

I was thinking about taking advantage of the Virtual Solution Center but not sure this falls into one of the categories listed. Gallery administration perhaps?

Thank you!

CristianoJ · Answer

As a test, try running the workflow in the designer inside the server.

You may also want to make sure that both your machine and the server's machine have installed a supported driver, and preferably both with the same version.

Data sources link for your reference: https://help.alteryx.com/current/DataSources/PostgreSQL.htm

If the test above works then try scheduling.

Keep in mind that the server most likely will have a Run As service account, thus when running through the scheduler it will  use that service account, therefore if there are permission restrictions you need to make sure that that service account will have the proper permissions to the data.