How to connect Postgre using linkserver

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to connect Postgre using linkserver

Author

Topic

zhangn
Starting Member

29 Posts

Posted - 2010-02-02 : 11:56:41

Hi all,

I am trying to connect the postgre database by using linkserver.

See the detail of postgre database below:

Host: loanpc
Port: 5432
Instance: GasForecasting
Database: Forecasting

I have run the following sql script in ssms:
EXEC sp_AddLinkedServer
@server = 'loanpc',
@srvproduct = 'PostgreSQL OLE DB Provider',
@provider = 'PostgreSQL',
@provstr = 'Server = loanpc; database = GasForecasting\WeatherForecasting;
uid = sysop; pwd = sysop'

--@catalog = 'public'
Go

EXEC sp_AddLinkedSrvLogin
@rmtsrvname ='loanpc',
@useself = 'FALSE',
@locallogin = 'uk\zhangn',
@rmtuser = 'sysop', -- User and password created in PostgreSQL pgAdmin
@rmtpassword = 'sysop'
GO

The link server has been created, but i am not able to connect.

See the error message below:

The test connection to the linked server failed:

Additional information:
An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.onnectionInfo)

Cannot create an instance of OLE DB provider "PostgreSQL" for linked server loanpc (Microsoft SQL server, error: 7302)

any thought will be appreciated!

Thanks again in advance!

Ning

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-03 : 06:58:03

Try to create an ODBC connection from the server instead and see if that works. Then you can create the link using the ODBC instead of using the OLE DB provider directly.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources