Using SQL 2008 link server to connect to Postgres by ODBC 64 bit driver - psqlodbc_09_00_0200-x64

Using SQL 2008 link server to connect to Postgres by ODBC 64 bit driver - psqlodbc_09_00_0200-x64 by Nenea Nelu

Closed

as External Help for as External

Type:	Bug
ID:	620247
Opened:	11/9/2010 11:34:08 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We are trying to move all connections/linked servers from SQL 2005 to SQL 2008 using the new PG psqlodbc_09_00_0200-x64 driver and getting the error below no matter what options/security etc we used.

SQL 2005 on Win 2003 using old psqlodbc_08_03_0400 32 bits is working fine
SQL 2008 on Win 2008 all 64 bits using PG psqlodbc_09_00_0200-x64 to create ODBC data sources then linked server in SQL do not work at all.

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

Funny thing is I can connect to the catalog (pg database) view all tables but when I try to script a select I get the error below:
“Cannot obtain the schema rowset “DBSCHEMA_COLUMNS” for OLE DB provider “MSDASQL” for linked server "pgprod_myanalytics". The provider supports the interface but returns a failure code when it is used.”

Does anyone had success linking a PG in SQL 2008 by using an ODBC created with psqlodbc_09_00_0200-x64 Unicode driver?

Needless to say that all necessary rights/permissions are in place.

Regards.
======

Added SQLDumps from the server where they occur after each

"Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "MSDASQL" for linked server "pgprod_myanalytics" reported an error. The provider reported an unexpected catastrophic failure."

error

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Windows Server 2008 R2

English

on SQL 2008
[Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)]

add a odbc data source by using psqlodbc_09_00_0200-x64 driver
add a linked server using Microsoft OLE DB for ODBC data type

run a query like:
SELECT * FROM OPENQUERY(pgprod_myanalytics,'select client_id as client_id from public.clients;')

IfI try to script a table from the linked server I get the second error posted below:

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

--error from scripting a table select in linked server
“Cannot obtain the schema rowset “DBSCHEMA_COLUMNS” for OLE DB provider “MSDASQL” for linked server "pgprod_myanalytics". The provider supports the interface but returns a failure code when it is used." Microsoft SQL error 7399

linked server query should return the same results as in SQL 2005 using exactly the same architecture, code credentials except the 32 bit ODBC driver to connect to PG.

X64

File Name	Submitted By	Submitted On	File Size
SQL2008Dumps.zip (restricted)	Nenea Nelu	11/10/2010	-

Posted by WillieMecalc on 4/19/2012 at 12:47 AM

I have tried this workaround but still see the same error. Can you suggest something else?

Posted by Nenea Nelu on 11/19/2010 at 7:49 AM

Maybe before this issue is closed it would be worth it to make sure that the future default installations of MSDASQL will have the "Allow Inprocess" unchecked as recommended below.

Other than that from our point of view please close it as this is not an issue anymore after the workaround was put in place.

Posted by Microsoft on 11/10/2010 at 5:09 PM

Dear customer,

Thanks for reporting this issue. Unfortunately, we can't extensively test more than a handful of third-party providers.

The problem here may be caused by the way MSDASQL is translating the OLEDB calls that SQL Server makes into ODBC. This translation in general is imperfect, so if there is a native OLE DB provider for PostgreSQL, you may get better results for that. It might also be helpful for troubleshooting to try using the earlier version of the provider with SQL Server 2008 -- perhaps something changed in the provider itself.

Regarding the workaround you suggest, unchecking "Allow inprocess" is actually the recommended setting for linked server providers in terms of stability, because that way the provider runs in a separate address space (there is a small performance overhead though).

Regards,

Vassilis Papadimos,
SQL Server Development.

Posted by Nenea Nelu on 11/10/2010 at 1:59 PM

Found that if I go to SQL 2008 server in SSMS, expand Server Objects -> Linked Servers -> Providers and right click MSDASQL then UNCHECK all inclding Allow In Process that was checked by default in SQL 2005 all is good and I can run queries against Postgres via linked server.