[ 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 15:00
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10109 87&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,@rmtus er=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 script using 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=En umerate+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 because the 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=204 76

------------------------------
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 catastrophic failure.
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;')






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

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc