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


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


  • 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 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
    3. In SQL Server Management Studio, create a linked server using the following command:

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

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




Publicado el 1 agosto, 2012 en SQL Server 2008. Añade a favoritos el enlace permanente. 7 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

  6. 我が国では、人口減少が深刻だと言われています。でも全体の人口の減少していくことよりも15-64歳の人口減少が著しいのです。という事は、わかりやすく言えば働き手不足という事なんです。つまり今後の求人では給料が上昇せざるを得ないのです。

  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


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

Logo de

Estás comentando usando tu cuenta de 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: