Postgres ODBC, Linked Server, SQL Server Reporting Services Connection

Connect to Postgres using ODBC connection follow these steps:

1. Download Postgres ODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/ 
2. Install above ODBC drivers
3. Create system DSN ... On windows XP > Control Panel > Administrative Tools > Data Sources (ODBC)
4. Go to System DSN Tab > Add > Select PostgresSQL Unicode sriver > Finish
5. On Prompted page add

Data Source --- Whatever you want to name the DSN
Database - Name of postgres database
Server - Name of Postgres server
Port:5432 (or whatever you database port is..)
Username and Password: Enter postgres database username /password you wish to use

Test the System DSN by clicking on Test

• Create Linked Server on SQL Server 2008/2005 to connect to postgres database

Start SQL server management studio
Connect to SQL server instance where who wish to create linked server
Go to ServerObject > linked Servers > New Linked Server
Fill the Linked server properties

Select Provider: Microsoft OLEDB Provider for ODBC Drivers
Product name: postgres (you can put whatever you like)
Data Source: Name of System DSN created in last step

Right click Linked Server and Test Connection
Now you query postgres from SSMS by something like



select * from linkedserver_name.database_name.schema_name.Table

• Creating Connection to Postgres from SQL Server Reporting Services (SSRS)

Create a New data Source
Name : Test
Data Source Type: ODBC
Connection String: Driver={PostgreSQL UNICODE};Server=testserver;Port=5432;Database=Test;Schema=public;
Connect using: Credentials stored security in report server, enter user_name, password and Test Connection

If it is 64-bit driver then
Connection string is something like
Driver={PostgreSQL 64-Bit ODBC Drivers};Server=testserver;Port=5432;Database=test;Schema=public;

Using above techniques you can also connect from SSIS (SQL server Integration Services)


Source: click here
OK