Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit

Environment

  • Machine 1
    IP: 10.2.29.183
    OS: Windows Vista Business SP1
    DB: PostgreSQL 8.3, port: 5432
  • Machine 2
    IP: 10.2.29.18
    OS: Windows Server 2003 Enterprise x64 Edition SP2
    DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900

Procedures

  • Machine 1
    1. Make sure port 5432 is opened in the firewall.
    2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all 10.2.29.18/32 md5 is added to the end of the file.
  • Machine 2
    1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
    2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case psqlodbc_08_03_xxxx.zip).
    3. In SQL Server Management Studio, create a linked server using the following command:

      EXEC sp_addlinkedserver
      @server = '10.2.29.183',
      @srvproduct = 'PostgreSQL',
      @provider = 'MSDASQL',
      @provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=10.2.29.183;Port=5432;database=my_db_name;pwd=my_password';
      EXEC sp_addlinkedsrvlogin
      @rmtsrvname = '10.2.29.183',
      @useself = 'false',
      @rmtuser = 'postgres',
      @rmtpassword = 'my_password';
    4. To test the connection:

      SELECT *
      FROM OPENQUERY([10.2.29.183], 'SELECT * FROM information_schema.tables')

Fuente: http://eddiecjc.blogspot.com/2009/05/set-up-postgresql-as-linked-server-in.html

Driver: http://www.postgresql.org/ftp/odbc/versions/msi/

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

  1. An outstanding share! I have just forwarded this
    onto a co-worker who was conducting a little homework on this.
    And he actually bought me lunch because I stumbled upon it for him.
    .. lol. So allow me to reword this…. Thank YOU for the meal!
    ! But yeah, thanks for spending the time to discuss this topic here on
    your site.

  2. Great beat ! I would like to apprentice while you amend your site, how could i subscribe for a blog site? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear concept

  3. It’s in fact very difficult in this active life to
    listen news on Television, thus I just use web for that reason, and take the most up-to-date information.

  4. By using three interdependent subsystems, the Aquaponics method facilitates
    the cultivation of plants and aquatic life forms in a confined space using purely organic methods.
    Aquaponics gives a great experience of staying all-around nature inside the clustered and chaotic cities.
    Bacteria like nitrobacter are designed for breaking
    down the ammonia that’s being continually manufactured by the fish.

  5. Buen aporte amigo chipollo

  1. Pingback: Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit « DbRunas – Noticias y Recursos sobre Bases de Datos

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: