'How can I connect to a remote SQL Server from Postgres using dblink?

I have a Postgres SQL query that retrieves all the data I need apart from one field, which is not stored. I have to connect to a SQL Server database to retrieve this additional field, which I am doing using dblink. I've used dblink in a few other queries, but only connecting Postgres to Postgres.

 SELECT
    cm.course_id AS "EXTERNAL_COURSE_KEY",
    cm.course_id AS "COURSE_ID",
    cm.course_name AS "COURSE_NAME",
    cm.start_date AS "START_DATE",
    '' AS "END_DATE",
    cm.available_ind AS "AVAILABLE_IND",
CASE
        WHEN cm.row_status = '0' THEN
        'ENABLED' 
        WHEN cm.row_status = '1' THEN
        'UNDEFINED' 
        WHEN cm.row_status = '2' THEN
        'DISABLED' ELSE'DELETED' 
    END ROW_STATUS,
    'TEMPLATE ID' AS "TEMPLATE_COURSE_KEY",
CASE
        WHEN cm.duration = 'C' THEN
        'CONTINUOUS' 
        WHEN cm.duration = 'R' THEN
        'RANGE' 
    END DURATION 
FROM
    course_main cm 
CROSS JOIN dblink ( 'host=<HOST> port=<PORT NUM> dbname=<DB NAME> user=<USERNAME> password=<PASSWORD>',
    'SELECT mle_id, template_id from mle_object;' ) -- accessing sql server db
    AS course_templates ( mle_id VARCHAR(255), template_id VARCHAR(255) )
WHERE
    cm.course_id = 'I3016-AMBS-60037-1192-06M-027386'

When I run the query, I get the error below.

ERROR: could not establish connection DETAIL: could not connect to server: Connection timed out Is the server running on host "[HOST]" ([IP ADDRESS]) and accepting TCP/IP connections on port [PORT NUM]

I can retrieve data from this sql server database in a separate query, but not using dblink.

Any help is appreciated.

thanks

sql-serverpostgresql


Solution 1:[1]

You can't. The dblink extension is only usable between Postgres instances.

If you need to access a SQL Server table, you will have to use a foreign data wrapper.

There is one for SQL Server (I only works on Linux if I'm not mistaken).

Once the wrapper is installed you can create a foreign table and join to that in your query.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions