How to access SQL server data from PostgreSQL (pgAdmin)?

Question

Need to access the SQL server data from pgAdmin. I have tried with foreign data wrapper, but it's not working. I'm trying to access the data from " dbo.example " which is in SQL server from pgAdmin. Please provide the prerequisites to achieve this.

Sample Code:

CREATE EXTENSION postgres_fdw;

CREATE SERVER mssql_pm_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '121.112.141.88', port '1874');

CREATE USER MAPPING FOR postgres
SERVER mssql_pm_server
OPTIONS ( user '1122', password '1122',dbname 'db_Product');    

CREATE FOREIGN TABLE mssql_pm_table1(
    BusinessEntityID int NOT NULL,
    TerritoryID text
)
SERVER mssql_pm_server 
OPTIONS (table_name  'dbo.example');

Error:

ERROR: could not connect to server "mssql_pm_server"

DETAIL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

SQL state: 08001

Laurenz Albe · Accepted Answer · 2018-07-26 09:37:14Z

3

You need the proper foreign data wrapper to access Microsoft SQL server. The PostgreSQL foreign data wrapper is to connect to PostgreSQL databases.

You should use tds_fdw.

answered Jul 26, 2018 at 9:37

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

112233 Over a year ago

could you please elaborate on this..bcz i'm new to this and not able access the link too.

Laurenz Albe Over a year ago

Every type of data source needs a specific foreign data wrapper. To access the link, you must get an internet connection.

112233 Over a year ago

please tell me how add this extension ERROR: could not open extension control file "C:/Program Files/PostgreSQL/10/share/extension/tds_fdw.control": No such file or directory SQL state: 58P01

Laurenz Albe Over a year ago

You did not install the foreign data wrapper. You have to compile and install it.

112233 Over a year ago

i have downloaded the tds_fdw file,but i dont know how to install this..could you please help me to do

|

Collectives™ on Stack Overflow

How to access SQL server data from PostgreSQL (pgAdmin)?

Sample Code:

Error:

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample Code:

Error:

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related