The TDS_FDW enables integration from PostgreSQL to SQL Server and Sybase (which both use the TDS protocol). TDS_FDW is available in the BigSQL distribution on Linux, OS X and Windows.
On Linux, you need to install FreeTDS:
yum install epel-release yum install freetds
Ensure that your SQL Server is properly configured to allow remote connections, is running the TCP/IP protocol and is listening on a static port. Ensure that the user in SQL Server is configured properly and has rights to the database and table you are trying to remotely access. For the sake of the example below, we assume you can succesfully connect to Microsoft SQL or Sybase from the user that runs Postgres using the tsql command line util that is part of FreeTDS:
tsql -S 10.30.3.147 -U tdstest
Our sample table in TDS is defined like this:
SELECT * FROM TestDB.INFORMATION_SCHEMA.Tables SELECT * FROM t1 Name Null? Type ------ --------- ----------- A NOT NULL NUMBER(38) B NOT NULL NUMBER(38)
First install the tds_fdw using BigSQL Manager or the PGC command line:
./pgc install tds_fdw1-pg95
Then configure PostgreSQL, from psql as the superuser, as follows:
postgres=# create extension tds_fdw; CREATE EXTENSION postgres=# create server TDS_TestDB foreign data wrapper tds_fdw options (servername '192.168.1.101', port '1433', database 'TestDB', tds_version '4.2', character_set 'UTF-8'); CREATE SERVER postgres=# grant usage on foreign server TDS_TestDB to postgres; GRANT postgres=# create user mapping for postgres server TDS_TestDB options (username 'tdstest', password 'tdspassword'); CREATE USER MAPPING postgres=# create foreign table t1 (a integer, b integer) server TDS_TestDB options (table 'T1'); CREATE FOREIGN TABLE
Now your remote table t1 can be used in SQL statements like any normal postgres table or view.
FreeTDS supports logging to a text file by setting the TDSDUMP environment variable. This log is especially helpful for debugging connection and security problems. Some error messages returned from SQL Server are not returned to psql (or another PostgreSQL client), but are logged in this file. Remember to restart your Postgres server after changing environment variables.
Learn more about using TDS FDW perusing its documentation.