56 lines
1.5 KiB
Markdown
56 lines
1.5 KiB
Markdown
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');
|