SQL Server add a linked server to postgres database

Question

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

• jjanes: Look in the PostgreSQL server's log file to see if it reported any errors.
• jjanes: that is highly configurable, so I don't know. How did you install PostgreSQL? What OS are you running it on?
• Dassy: This is my select I'm running in SSMS: Select * from openquery(LINKED_SERVER_NAME,'Select * from Database.schema.tablename')
• Dassy: Error is cross-database references are not implemented. What do I do to fix this?

Answer 1

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"')