-- 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)
-- 3. Setting ODBC
o click ok button
-- 4. add linked server on sql server
-- 5. checking MSDASQL
-- 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/
댓글목록
등록된 댓글이 없습니다.