Connection to PosgreSQL Linked Server failing

Question

Hi All,
I am trying to create a linked server to a PostgreSQL server. I can test the connection to the Windows ODBC both 64 and 32 successfully, and I have followed the following link to create a linked server
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration-part-2/
Still I am getting the following error message when I try to create a linked server , please advice.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "POSTGRESQL1".
OLE DB provider "MSDASQL" for linked server "POSTGRESQL1" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "POSTGRESQL1" returned message "timeout expired
". (Microsoft SQL Server, Error: 7303)

Thanks

Answer 1

Linked servers are often difficult, because there are so many components and error message are often cryptic. And using MSDASQL + ODBC does not make things easier since that adds one more component.

I think you should try an OLE DB provider for Postgres instead: https://www.pgoledb.com/.

Myself, I have one from Intellisoft that I have used very occasionally, and I don't know if that one is still current.

Answer 2

Hi @Beacon77 ,

Welcome to Microsoft Q&A!
According to your error message, you can try to set the remote query timeout option to 0(infinite) and then observe if there are any other new errors.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.