blog.rhp.org

Install Postgresql using the bigsql distro

Follow the instructions at the install page.

Install tds_fdw1-pg96

C:\bigsql>pgc install tds_fdw1-pg96

Setup a database and user in SQL Server and a test table

CREATE TABLE test1 (id integer, name varchar(255));
INSERT INTO test1(id, name) VALUES (1, 'hello, world'), (2, 'I am a banana');

Set up the extension in postgres

CREATE EXTENSION tds_fdw;
CREATE SERVER 
    tds_test_server 
    FOREIGN DATA WRAPPER tds_fdw 
    OPTIONS 
        (
         servername 'localhost', database 'test', 
         port '1433', msg_handler 'notice', character_set 'UTF-8',  tds_version '7.1'
        );

CREATE USER MAPPING FOR 
    testdev 
SERVER 
    tds_test_server OPTIONS (username 'username', password 'password');

Note that if you do not specify character_set, you will get an unable to connect error and you will bang your head against the wall for hours

Create the foreign table

CREATE FOREIGN TABLE test1 (id integer, name varchar(255)) SERVER tds_test_server OPTIONS (table 'dbo.test1');

Query your new table

SELECT * FROM test1;

Enjoy!

Filed under:
Uncategorized by Richard Pistole