Sql-server – SQL Server add a linked server to postgres database

linked-serverpostgresqlsql server

I am trying to connect a PostgreSQL to SQL Server server as a linked server.

On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN work correctly.

Using SSMS I created a linked server that created successfully.
When I test the connection it says successful and when I expand I can see all the tables from postgres.

However when I try selecting from one of those tables it goves me the error:
The OLE DB provider "MSDASQL" for linked server "ServerName" reported an error. The provider did not give any information about the error.

I googled around but did not find anything that was helpful for me.
Can someone please help me with this.

Thank you

Best Answer

PostgreSQL:

  1. You must provide a database name in the Data Source DSN.
  2. Run Management Studio as Administrator

  3. You must omit the DBName from the query:

    SELECT * FROM OPENQUERY([LinkedServer], 'select * from schema."tablename"')

Related Question