[ psqlodbc-Bugs-1010987 ] Linked Server fails from SQL Server 2008/5 and PostgreSQL 8.4 using SSL

Bugs item #1010987, was opened at 2011-02-03 16:00
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010987&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: paddy carroll (pcarr01)
Assigned to: Nobody (None)
Summary: Linked Server fails from SQL Server 2008/5 and PostgreSQL 8.4 using SSL

Initial Comment:
1) I create an ODBC DSN onto my Postgres Server from the Windows(32) platform on which the SQLServer resides
2) Test connection - Test Succeeds
3) Successfully Run an ado test via a VBScript to get a count(*) from my Postgres table
My table is

extract=# \d xcc_qos
                 Table "public.xcc_qos"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 po_fad_code   | character varying(9)        |
 start_tsmp    | timestamp without time zone |
 ongoing_msecs | bigint                      |
 elapsed_msecs | bigint                      |
 rec_date      | date                        |
Indexes:
    "pk_xcc_qos" UNIQUE, btree (po_fad_code, start_tsmp)

which is inherited by a few others like:
extract=# \d xcc_qos_07_11_2010
            Table "public.xcc_qos_07_11_2010"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 po_fad_code   | character varying(9)        |
 start_tsmp    | timestamp without time zone |
 ongoing_msecs | bigint                      |
 elapsed_msecs | bigint                      |
 rec_date      | date                        |
Check constraints:
    "xcc_qos_07_11_2010_cst" CHECK (rec_date = '2010-11-07'::date)
Inherits: xcc_qos

4) From SQL Server I add my linked server:

/****** Object:  LinkedServer [DXC]    Script Date: 02/03/2011 15:02:29 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DXC', @srvproduct=N'DXC', @provider=N'MSDASQL',
@datasrc=N'PostgreSQL30',@catalog=N'extract' 
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'DXC',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DXC', @optname=N'use remote collation', @optvalue=N'true'

5) All looks good in the SQL Server management studio, I can see all the tables but when I try to generate a select
scriptusing the management studio GUI I Get the following error: 
TITLE: Microsoft SQL Server Management Studio
------------------------------

Enumerate columns failed for LinkedServer 'DXC'.  (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+columns+LinkedServer&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)". Conversion failed
becausethe data value overflowed the data type used by the provider. (Microsoft SQL Server, Error: 7346) 

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7346&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

5) If I try to run a pass throgh query using openquery
select * from openquery(DXC,'select count(*) from xcc_qos')
I get:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "DXC" reported an error. The provider reported an unexpected
catastrophicfailure. 
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "DXC".

6) But the following openquery works:
select * from openquery(DXC,'select 1;')






----------------------------------------------------------------------

Comment By: tor ge (torge)
Date: 2011-11-01 09:26

Message:
Hi,
with the latest snapshot 9.0.0311 (2011-10-29) at http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/index.html the
problemsolved for me (used 32 Bit Unicode)! 

Bye,
Torsten



----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010987&group_id=1000125