473,703 Members | 2,335 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PostgreSQL Linked server question with MSSQL2K

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?


Nov 23 '05 #1
0 10590

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

Similar topics

1
2437
by: John H | last post by:
Can anyone help with why our pages are slow to load during peak-use hours? We run a database site getting 4 million hits a month which becomes painfully slow during peak hours. The slowness is not reflected in Coldfusion's debug output; for example the total run-time of 5 heavy queries is typically 1000 ms, but during peak times the page does not load for 4-10 seconds. Processor activity at peak times is "busy, but not flat-out"...
0
3855
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
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
26
10813
by: jini us | last post by:
Hi, I am starting a new project where I intend to use embedded database server in my win32 application. I intend to use VC++ microsoft studio 6.0 as my development environment. The postgres.org website seems to be catering for people with all sorts of requirements and platforms.
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 ?
2
3890
by: Zlatko Matić | last post by:
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as...
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...
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
3
5484
by: Sim Zacks | last post by:
I have a postgresql backend with an access front end and I am trying to redefine the recordset of the form to use an ADO recordset. The problem is that the CursorType always changes to AdOpenStatic, even if I choose adOpenDynamic. If anyone has any thoughts, please let me know. The form works great for viewing, but I cannot update or insert any new records. Below is the code I am using: connectstr = "DSN=SAP_PG;uid=postgres" Set conn...
14
2600
by: jptpjs via AccessMonster.com | last post by:
I have an application I built in Access 2003. It is an electronic medical records program split into front end (8 computers) and back end. This runs flawlessly. Purchased a billing program that uses Postgresql. This resides on the same computer as our Access backend. We keep getting knocked out of the new billing program and never get knocked out of my Access program. The billing software provider says that Access and Postgresql running...
0
8758
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
9251
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
9121
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...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8962
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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
4432
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...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.