Tables are not showing in dataset for postgresql in synapse

Question

Tables are not showing dataset for postgresql data base even after linked service connection is successful. Tried with lookup activity also to fetch the data from the postgresql database, it failed with the below error.

    "errorCode": "2100",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: Something went wrong describing the statement(),Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: Something went wrong describing the statement(),Source=mspsql27.,'",
    "failureType": "UserError",
    "target": "Lookup1",
    "details": []

Answer 1

I think your issue is between Azure Synapse Analytics and the PostgreSQL database through the ODBC connection.

If you can connect and view tables outside of Synapse, then log in to the PostgreSQL database and verify permissions for the account being used:

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'your_username';

Aslo, there might be an issue with the ODBC driver being used. Make sure you're using a compatible and updated version of the ODBC PostgreSQL Wire Protocol driver.

Please try and tell us :)

Answer 2

Thank you, Amira Bedhiafi

Hello Charan,

I am checking to see if you got a chance to look into the above answer from Amira.

In addition, can you please let us know if this is failing intermittently or if you are getting this error every time?

If this is failing intermittently, this could be an issue with the PostgreSQL driver issue.

Also, please check the driver, and the SHIR version you are using is the latest.

for the driver:

ms_postgresql_71\packaging\ODBC_Drivers\Microsoft_PostgreSQL_ODBC_Driver\driver64\mspsql27.dll