472,811 Members | 1,183 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,811 software developers and data experts.

PostgreSQL Linked Server question

I have to update a PostgreSQL linked server through MSSQL2K.

I first configured the connection with ODBC as follows and I can do queries
with no problem:

EXEC sp_droplinkedsrvlogin @rmtsrvname = 'PostgreSQL', @locallogin = NULL
GO

EXEC sp_DropServer 'PostgreSQL'
GO

EXEC sp_AddLinkedServer
@server = 'PostgreSQL',
@srvproduct = 'Microsoft OLE DB Provider for ODBC Driver',
@provider = 'MSDASQL',
@datasrc = 'PostgreSQL', -- a previously created and configured ODBC data
source
@location = 'localhost',
@catalog = 'public'
GO

EXEC sp_AddLinkedSrvLogin
@rmtsrvname = 'PostgreSQL',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'postgre', -- User and password created in PostgreSQL pgAdmin
@rmtpassword = 'password'
GO

SELECT * FROM OPENQUERY(PostgreSQL, 'SELECT * FROM "Customer"')
SELECT * FROM OPENQUERY(PostgreSQL, 'SELECT "CustId", "CustName" FROM
"Customer"') -- ** Notice CustId column **

FYI, I SHOULD be able to use SELECT * FROM PostgreSQL...Customer, but I get
this message:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].

But when I specify the schema/catalog, I get this message:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'.
A four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

When I try to update the PostgreSQL linked server with:

UPDATE OPENQUERY(PostgreSQL, 'SELECT * FROM "Customer" WHERE "CustId" =
''WBJ''') SET "CustName" = 'Test name'

The server returns:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: ERROR: column "custid" does not exist]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].

Well, custid DOES exist in the customer table and this makes no sense.

So, I tried to use - PostgreSQL OLE DB Provider:

EXEC sp_droplinkedsrvlogin @rmtsrvname = 'PostgreSQL', @locallogin = NULL
GO

EXEC sp_DropServer 'PostgreSQL'
GO

EXEC sp_AddLinkedServer
@server = 'PostgreSQL',
@srvproduct = 'PostgreSQL OLE DB Provider',
@provider = 'PostgreSQL',
@provstr = 'Password=password;User ID=postgre;Location=database',
@datasrc = 'localhost',
@catalog = 'public'
GO

EXEC sp_AddLinkedSrvLogin
@rmtsrvname = 'PostgreSQL',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'postgre', -- User and password created in PostgreSQL pgAdmin
@rmtpassword = 'password'
GO

The linked server is successfully created, but when I try to run a query, I
get this message:

Server: Msg 7302, Level 16, State 1, Line 2
Could not create an instance of OLE DB provider 'PostgreSQL'.
OLE DB error trace [Non-interface error: CoCreate of DSO for PostgreSQL
returned 0x80040154].

From reading Internet posts, I know that the 'PostgreSQL OLE DB Provider' is
buggy, but I need to update the PostgreSQL database in some way from SQL
Server because of the design of the application.

Can someone help getting the ODBC linked server running in a way where I can
run UPDATE? Is there just something wrong with my syntax? Can someone help
get the OLE DB provider running?
Oct 3 '05 #1
0 3750

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mike | last post by:
Hi, I have an architectural type question that centers around php. We have a website that people hit that uses php to dynamically generate web pages. We use Apache and php (although we don't have...
125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
0
by: Bill J. | last post by:
I have to update a PostgreSQL linked server through MSSQL2K. I first configured the connection with ODBC as follows and I can do queries with no problem: EXEC sp_droplinkedsrvlogin @rmtsrvname...
13
by: Zlatko MatiŠ | last post by:
Is it possible to use Access as front-end for POstgreSQL and how ? What about Access Projects (.adp) and PostgreSQL ?
20
by: Keith G. Murphy | last post by:
I'm trying to get a feel for what most people are doing or consider best practice. Given a mod_perl application talking to a PostgreSQL database on the same host, where different users are...
3
by: Robert Abi Saab | last post by:
Hi everyone. I just finished a course on PostgreSQL and I found out that PostgreSQL doesn't provide any object relational features (as claimed in the official documentation), except table...
1
by: Richard Huxton | last post by:
On Thursday 12 February 2004 20:25, Prashanthi Muthyala wrote: > Hi Richard Hi Prashanthi - nice to hear from you again. I've taken the liberty of cc-ing the general list on this, since there...
0
by: Willy SkjŠveland | last post by:
Hi, Is it possible to define "linked servers" like in MS-SQL? Or - is any work going on to implement such feature? So that using 2 postgresql DB's where one is in dmz and one in production -...
17
by: Jim Strickland | last post by:
We currently are running a data intensive web service on a Mac using 4D. The developers of our site are looking at converting this web service to PostgreSQL. We will have a backup of our three...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.