notes/tds.md

install tds on ubuntu to connect to mssql from pgsql

https://github.com/tds-fdw/tds_fdw/blob/master/InstallUbuntu.md

copy and build tds_fdw:

export TDS_FDW_VERSION="2.0.3"
sudo apt-get install wget
wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz
tar -xvzf v${TDS_FDW_VERSION}.tar.gz
cd tds_fdw-${TDS_FDW_VERSION}/
sudo chown ptrowbridge:ptrowbridge -R "tds_fdw-${TDS_FDW_VERSION}/"
make USE_PGXS=1
sudo make USE_PGXS=1 install

create extension in postgres: CREATE EXTENSION tds_fdw;

create foreign server:

CREATE SERVER usmidsql01 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'usmidsql01', port '1433', database 'fanalysis', tds_version '7.1');

create user mapping:

CREATE USER MAPPING FOR ptrowbridge SERVER usmidsql01 OPTIONS (username 'Pricing', password '');

to extract the schema into a single table that describes the schema do:

IMPORT FOREIGN SCHEMA dbo FROM SERVER usmidsql01 INTO pricequote_dbo;

and this will create a table call pricequote_dbo."UNCONTRAINED_COLUMNS"

create foreign table:

CREATE FOREIGN TABLE pricequote.pl (
    quote integer
    ,billto text
    ,shipto text
    ,cdate timestamp
    ,value numeric(18,9)
    ,title text
    ,descr text
    ,comment text
    ,url text
    ,srce text
)
SERVER usmidsql01 OPTIONS (table_name 'fanalysis.rlarp.pl')

to link in fanalysisp CREATE SERVER usmidsql01_fanalysisp FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'usmidsql01', port '1433', database 'fanalysisp', tds_version '7.1');