|
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?
| 
