notes/tds.md

56 lines
1.5 KiB
Markdown
Raw Permalink Normal View History

2023-08-22 16:06:56 -04:00
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}/
2023-08-22 16:11:27 -04:00
sudo chown ptrowbridge:ptrowbridge -R "tds_fdw-${TDS_FDW_VERSION}/"
2023-08-22 16:06:56 -04:00
make USE_PGXS=1
sudo make USE_PGXS=1 install
```
create extension in postgres:
`CREATE EXTENSION tds_fdw;`
create foreign server:
```
2023-08-22 16:09:40 -04:00
CREATE SERVER usmidsql01 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'usmidsql01', port '1433', database 'fanalysis', tds_version '7.1');
2023-08-22 16:06:56 -04:00
```
create user mapping:
2023-08-22 16:09:40 -04:00
```
CREATE USER MAPPING FOR ptrowbridge SERVER usmidsql01 OPTIONS (username 'Pricing', password '');
```
2023-08-22 16:06:56 -04:00
to extract the schema into a single table that describes the schema do:
2023-08-22 16:09:40 -04:00
```
IMPORT FOREIGN SCHEMA dbo FROM SERVER usmidsql01 INTO pricequote_dbo;
```
2023-08-22 16:06:56 -04:00
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')
```
2023-11-06 08:55:56 -05:00
to link in fanalysisp
CREATE SERVER usmidsql01_fanalysisp FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'usmidsql01', port '1433', database 'fanalysisp', tds_version '7.1');