1. Initially download the ODBC driver for Postgres form the below website    http://www.postgresql.org/ftp/odbc/versions/msi/

2. Install the driver in your MS SQL Server

3. Create System DSN in your ODBC data source administrator

image

4. Click add botton in User DSN and add the required details of your Postgres sql database

image

Note: You could also test the connection by clicking on test button on bottom right cornor of the console.

5. Now open your MS SQL server management studio and expand the server object

6. Right click link server and open new linked server

7. New linked Server properties appears. fill the properties and click ok

 

image

– Type in the linked server name

– Choose Microsoft OLEDB Provider for ODBC Drivers provider

– Enter Product name of the PostgreSQL ODBC driver

– Enter the data source

– Enter the provider string with the following format   “Driver{PostgreSQL UNICODE};Server=0.0.0.0;Port=5432;Database=DBName;Schema=public;username=dbuser,password=******;”

Test your connection.

Note:  If it is 64-bit driver Provider string will be Driver={PostgreSQL (x64)  UNICODE};Server=0.0.0.0;Port=5432;Database=dbname;Schema=public;username=dbuser;password=*****;

Advertisements