Using SQL Server 2008 R2 Linked Servers with PostgreSQL 64-bit

Having setup a Linked Server in Management Studio talking to a PostgreSQL 8 database I encountered the following error when attempting to run any valid query:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "PG_SERVER" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PG_SERVER".

After some digging I came across a handy article on Microsoft Connect describing the same issue, with thanks to Nenea Nelu here’s the solution…

  • Expand Server Objects > Linked Servers > Providers. 
  • Right-click on MSDASQL and select Properties…
  • In the Properties dialogue un-check “Allow inprocess” as follows…
  • Click OK and re-run your query. 

Hopefully that should solve your problem, please note that this will affect all Linked Servers using that provider however as the Connect article points out – this is best practice for linked servers anyway.