Hello. I had the linked server working previously, but I have recently upgraded my SQL Server and am having to re-install the linked servers. So I know it is not a box/os issue since it's the same box.
I then followed the steps in the following post: [
www.pgoledb.com/forum/viewtopic.php?f=4&...hilit=+linkedtest#p6
]
However, I am still getting this error message:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "PGNP" for linked server "PGNP_SAMPLES" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "PGNP" for linked server "PGNP_SAMPLES".
My setup is as follows:
Both Sql server and Postgres are on the same box.
SQL Server 2008 R2 Developer Edition
Postgres psql version 8.4.7.1181
PGNP version 1.3.0.2251
Box is Windows 7 Professional SP1
I have verified that the linkedtest database is setup and able to query via PGPAdmin.
I have verified that the PGNP Provider in Sql Server is set to have AllowInProcess flagged as 1
The query I'm trying to run is
select * from openquery(PGNP_SAMPLES, 'select * from contact');
when I receive the above errors.
When trying to expand the linked server down to catalogs-default-tables in SQL Server mgmt studio I get the following error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The OLE DB provider "PGNP" for linked server "PGNP_Samples" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "PGNP" for linked server "PGNP_Samples". (Microsoft SQL Server, Error: 7399)
Does anyone have any suggestions?
Thanks!
