Getting error while creating Linked servers

Question

Post reply

Getting error while creating Linked servers

gangadhara.gorantla

SSC Journeyman

Points: 80
More actions
September 15, 2015 at 11:38 am

#321248

Hi,
below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
Can someone please guide me how to setup the linked server........... Below are the drivers installed on the SQL server PostgreSQL35W
PostgreSQL30
Thanks
Ganga

Viewing 3 posts - 1 through 2 (of 2 total)

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

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 1

Hi

Two points:

1. @datasrc - host name of postgres service, ping to be sure that dns resolves it correctly.

2. Try to set ip and port in @provstr.

Best regards

Mike

gangadhara.gorantla SSC Journeyman Points: 80 More actions · Answer 2

Hi Mike,

After making changes I am still getting the same error

C:\Users\ggorantla>ping 172.16.20.159

Pinging 172.16.20.159 with 32 bytes of data:

Reply from 172.16.20.159: bytes=32 time=4ms TTL=63

Ping statistics for 172.16.20.159:

Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

Minimum = 4ms, Maximum = 4ms, Average = 4ms

C:\Users\ggorantla>

/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123;Server=172.16.20.159;Port=5432'

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'