Tuesday, October 9, 2012

Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address http://www.postgresql.org/ftp/odbc/versions/msi/

Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)

image

2. Install ODBC Driver for PostgreSQL

image

image

3. Create connection to PostgreSQL database with ODBC Driver

image

image

image

image

4. Now, it’s time to create linked server to PostgreSQL database:

You can create with GUI

image

Or can create with scripts

EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

Finally, you can query PostgreSQL database inside SQL Server

image

5. Verify the linked server

image

1 comment:

  1. Hi I have error: Microsoft SQL Server, Error: 7399.
    Can you help me?

    ReplyDelete