Connecting Tech Pros Worldwide Help | Site Map

PostgreSQL Linked server question with MSSQL2K

 
LinkBack Thread Tools Search this Thread
  #1  
Old November 23rd, 2005, 02:04 AM
Bill J.
Guest
 
Posts: n/a
Default 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_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?





 

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Popular Articles

What is Bytes?

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 220,662 network members.