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