Linked server between sql server 2008 64 bit to post gre sql 32 bit

  • We have a Post Gre SQL running on a 32 bit machine. I need to create a linked server from a SQL server 2008 R2 instance on a 64 bit windows 2008 machine to a post Gre SQL on 32 bit machine. The post gre sql is on 8.1 version.

    I created system DSN using the odbc driver downloaded from this link

    http://code.google.com/p/visionmap/wiki/psqlODBC

    The connection works fine on the DSN.

    The linked server gets created. But when I test the linked server I get the following error:

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server " TEST".

    OLE DB provider "MSDASQL" for linked server " TEST" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

    I am not sure if the driver I am using the problem or is there some other issue with my script.

    Here is the script I used to creating the linked server:

    EXEC master.dbo.sp_addlinkedserver

    @server = N'TEST',

    @srvproduct=N'PostgreSQL35W',

    @provider=N'MSDASQL',

    @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=lichen;Server=

    rtserver;Port=5432;database=uname;pwd=pwd'

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST'

    ,@useself=N'True',@locallogin=NULL,@rmtuser=uname,@rmtpassword=pwd

    If there is anything you can help me with that would be great.

    Thanks in Advance.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply