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.
Thanks for posting this! I was getting the error:
“Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – The pipe has been ended.)”
I searched for hours before coming across this article. Unchecked “allow inprocess”, now I can connect to the linked server flawlessly!
Thank you SO much. I was looking for the reason for this error:
Error message: Cannot find either column “column_name” or the user-defined function or aggregate “column_name.ToString”, or the name is ambiguous.
SQL Server 2008 R2.
Thank you. You saved my life.
Well, at least you saved one working day.