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

4
  • Look in the PostgreSQL server's log file to see if it reported any errors.
    – jjanes
    Feb 5, 2020 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, 2020 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, 2020 at 16:31
  • Error is cross-database references are not implemented. What do I do to fix this?
    – Dassy
    Feb 6, 2020 at 14:49

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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