0

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

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

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.