473,703 Members | 3,036 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_droplinkedsr vlogin @rmtsrvname = 'PostgreSQL', @locallogin = NULL
GO

EXEC sp_DropServer 'PostgreSQL'
GO

EXEC sp_AddLinkedSer ver
@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_AddLinkedSrv Login
@rmtsrvname = 'PostgreSQL',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'postgre', -- User and password created in PostgreSQL pgAdmin
@rmtpassword = 'password'
GO

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

FYI, I SHOULD be able to use SELECT * FROM PostgreSQL...Cu stomer, 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(Postg reSQL, '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_droplinkedsr vlogin @rmtsrvname = 'PostgreSQL', @locallogin = NULL
GO

EXEC sp_DropServer 'PostgreSQL'
GO

EXEC sp_AddLinkedSer ver
@server = 'PostgreSQL',
@srvproduct = 'PostgreSQL OLE DB Provider',
@provider = 'PostgreSQL',
@provstr = 'Password=passw ord;User ID=postgre;Loca tion=database',
@datasrc = 'localhost',
@catalog = 'public'
GO

EXEC sp_AddLinkedSrv Login
@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 3855

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

Similar topics

3
3318
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 php compiled in with apache). Our Unix like server (FreeBSD) that has php (and Apache) on it that today communicates with the postgresql database on another Unix like server (Linux). I configured the php set of libraries with postgresql support...
125
14760
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 software giant such as Microsoft SQL Server, Oracle, and Sybase? Is PostgreSQL reliable enough to be used for high-end commercial application? Thanks
0
2165
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 = 'PostgreSQL', @locallogin = NULL GO EXEC sp_DropServer 'PostgreSQL' GO
13
7284
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
6625
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 logging onto the web server using LDAP for authentication, do most people 1) have the web server connecting to the database using its own user account (possibly through ident), and controlling access to different database entities strictly through...
3
6198
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 inheritance and very limited user defined types (I defined a UDT with 2 attributes and couldn't use it in a table, and the trainer said it must contain 1 attribute at most so that it can be used (as a column) in tables) So my question is whether...
1
3360
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 may be others who can help here. > I am trying to migrate the database and its tables from mysql in my > windows machine to postgresql in my new red hat linux . I was following > your links which has converstions from msaccess,mysql to postgresql...
0
1243
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 - the last one has defined the one in dmz as a "linked server" and sql's sent to the internal DB can do joins between tables in internal DB and DB in dmz? Reg. WS
17
3437
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 production servers at our location. The developers are recommending that I purchase a 2GHz Dual Processor G5 with between 2GB and 4 GB RAM. They say that this configuration would be able to easily run a copy of all three production servers. My...
0
8759
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9252
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9122
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7872
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4433
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2455
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.