Connection to PosgreSQL Linked Server failing

Beacon77 131 Reputation points
2022-04-12T18:16:20.91+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    2022-04-12T21:40:26.453+00:00

    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.


  2. Seeya Xi-MSFT 16,451 Reputation points
    2022-04-13T05:21:34.597+00:00

    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.