Postgresql linked sever in Sql server

Question

Connection status is success.

Query 1: select * from openquery(XYZ,'select * from People')

Status : Success

Query 2: select * from XYZ.NewDB.[public].People

status: FAiled.

Error :

OLE DB provider "MSDASQL" for linked server "XYZ" returned message "ERROR: cross-database references are not implemented: "NewDb.public.people";
Error while executing the query".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table ""newDB"."public"."people"" from OLE DB provider "MSDASQL" for linked server "xyz".

Please help..

Thanks in Advance...

Answer 1

It appears to be a limitation with the ODBC driver you are using.

Intellisoft has an OLE DB provider for Postgres, and using a linked server set up with this provider, I was able to query a table in my Postgres database in Azure using four-part notation.

Answer 2

Hi @Raju MP ,

It seems to be related to the driver you are using.

Please refer to the following article, which introduces the use of a linked server to query Postgresql with a four-part naming.
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration--part-2/