1

I wrote this PostgreSQL code lines, but I'm having the following error:

ERROR: could not connect to server "server\sqlexpress"

SQL state: 08001

What am I doing wrong?

My code:

USING PostgreSQL 9.3 on Windows 8.1 and MS SQL Express 2012 on same windows

  CREATE EXTENSION posgres_fdw;
  Query returned successfully with no result in 11 ms. 

  CREATE SERVER "server\SQLExpress" FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host 'localhost', dbname 'andesmar'); 
  Query returned successfully with no result in 12 ms.

  CREATE USER MAPPING FOR public SERVER "server\sqlexpress" 
    OPTIONS (user 'sa', password '1234'); 
  Query returned successfully with no result in 12 ms.

  CREATE FOREIGN TABLE datosplataforma (
     id                 bigint NOT NULL,
     messagedate        date NOT NULL,
     receiveddate       date NOT NULL,
     latitude           real,
     longitude          real,
     GPSspeed           real,
     bearingangle       integer,
     odometer           integer,
     eventtypeid        integer,
     vehicleid          integer,
     alertid            integer
   )
   SERVER "server\sqlexpress";
   Query returned successfully with no result in 12 ms.

   select * from datosplataforma; 
     ERROR: could not connect to server "server\sqlexpress"
     SQL state: 08001
T.C.
  • 133,968
  • 17
  • 288
  • 421
Capanga
  • 77
  • 1
  • 8

2 Answers2

4

postgres_fdw connects to another PostgreSQL server, not MS SQL Server.

If you want to connect to MS SQL, you need to use an ODBC or JDBC FDW with an MS SQL ODBC or JDBC driver, or to use a dedicated MS SQL FDW.

See: http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
0

I have met the same error too. If the server is the same as your current server, you should not mention the host and the port, maybe you need to code:

CREATE SERVER "server\SQLExpress" FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'andesmar');

The others are same.

Ale
  • 917
  • 9
  • 28
jhostman
  • 1