Postgres ODBC, Linked server, SQl server Reporting Services connection

Connec to Postgres using ODBC connection follow these steps
1) Download Postgres ODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/
or a 64-bit postgres ODBC driver from http://code.google.com/p/visionmap/downloads/detail?name=setup_psqlODBC_x64_8.3.4.0.exe&can=2&q=
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 whaever you database port is..)
Username and Password: Enter postgres database username /password you wish to use
Test the System DSN by clicking on Test
6) 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 u like)
Data Source: Name of System DSN created in last step
OK
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
7) 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 securily in report server, eneter 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)

Fuente: http://postgresblog.blogspot.com/2010/10/postgres-odbc-linked-server-sql-server.html

Publicado el 1 agosto, 2012 en SQL Server 2008. Añade a favoritos el enlace permanente. 5 comentarios.

  1. My programmer is trying to persuade me to move to .net from PHP.
    I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using Movable-type on numerous websites for about a year and am concerned about switching to another platform.
    I have heard fantastic things about blogengine.net. Is there a
    way I can transfer all my wordpress content into it?
    Any help would be really appreciated!

  2. Its such as you read my thoughts! You seem to grasp so much approximately this, like
    you wrote the e book in it or something. I feel that you simply could do with some percent to
    force the message house a bit, however other than that,
    that is wonderful blog. An excellent read. I’ll definitely be back.

  3. Water is the single most important factor in staying alive, and
    in an apocalyptic even finding clean water mater may
    be a challenge. The Charge includes a removable pocket clip and also includes the option for a Leather or Nylon sheath.

    In all truthfulness, I have done just about everything possible to and with
    that knife but have not appreciably hurt it in any manner.

  4. Hi! I’ve been following your site for some time now and finally got the bravery to go ahead and give you a shout out from Dallas Tx! Just wanted to mention keep up the great work!

  5. Determining equipment you have, subsequently seeking appropriate car owners after which you can
    installing them plus creating typically takes time and effort.
    Dell offers several different colors for their notebook
    computers, with some colors being specific to certain models to help them stand out.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: