Thursday, February 14, 2013

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

No comments: