Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server between sql server 2008 64 bit to post gre sql 32 bit Expand / Collapse
Author
Message
Posted Wednesday, March 21, 2012 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 6:38 AM
Points: 116, Visits: 365
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.
Post #1270242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse