[SQL] Connect to PostgreSQL on SQL Server via ODBC or OLEDB

/****************************************************************************************************************
-- Title : [SQL] Connect to PostgreSQL on SQL Server via ODBC or OLEDB
-- Reference : blog.slalom.com
-- Key word : sql server postgresql connect linked server odbc linked_server pgnp msdasql
****************************************************************************************************************/

-- 1. creating table on postgresql

drop table tbl_addr;

create table tbl_addr

( seq serial not null

, name varchar(50)

, city varchar(20)

, ctry varchar(20)

);

insert into public.tbl_addr (name, city, ctry)

values ('hayden', 'koisan', 'korea')

     , ('jason', 'suwon', 'korea')

     , ('subi', 'seosan', 'korea')

     , ('joyce', 'Pasig', 'phillippine')

     , ('nicole', 'newyork', 'usa');

    

select * from public.tbl_addr; 

-- 2. Installation of drivers(32-bit & 64-bit)

    o Download the latest .msi file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/ 

    o Execute downloaded .msi file.


-- 3. Setting ODBC

    o click management tool 

    o click data provider(ODBC)

               o click add button

                  o select PostgreSQL Unicode
 

                         o fill out this form like above and click test button

                                                       o see that message box
 

               o confirm added odbc source(PGSQLODBC)
               o click ok button


-- 4. add linked server on sql server

                                         o click right button and new linked server
                                      

    o fill in the form like above on general page

    o input account and password on security page

    o change RPC and export RPC to TRUE on server option page

                                         o confirm added linked server and tables

 

-- 5. checking MSDASQL

   o be sure to check "Allow Inprecess"


-- 6. testing connection to Postgresql on SQL Server
--    if possible, use upper case to linked server name.

-- via  4-partname

-- error occurred. I don't know why not.

select * 

from LINKED_PGSQL...tbl_addr;

insert into LINKED_PGSQL...tbl_addr.postgres.publick.tbl_addr (name, city, ctry) 

values ('tom', 'london', 'england');

-- via openquery

select *

from openquery(LINKED_PGSQL, 'select * from public.tbl_addr');

-- via execute(exec) at

execute('select * from tbl_addr where ctry = ?', 'korea') at LINKED_PGSQL;

execute('insert into tbl_addr (name, city, ctry) values (?, ?, ?)', 'tom', 'london', 'england') 

at LINKED_PGSQL; 

-- ref.Using PGNP which is commercial product via OLEDB
http://www.pgoledb.com/