Return text from PostgreSQL (9.6) to SQL Server from linked server query

I am trying to get text (15002 characters long) from PostgreSQL using a linked server from SQL Server 2012, using the official PostgreSQL ODBC driver in Windows.

I try to pull data from Postgres using the following query:

create table #temp
(id int, g nvarchar(max) ) 
insert into #temp 
exec ('select id ,st_astext(geom) from spatialdata') at pg96odbc

This returns correct length data, but the text is truncated to ~4000 characters; the rest of the row contains null values. I see this using the following query (on SQL Server):

select id, g from #temp for xml auto

This works correctly in the other direction (Postgres pulling from SQL Server) using odbc_fdw and the SQL Server native driver.

Question: Is this a bug, or is there an ODBC/SQL Server option to get it to return all data, or a way to return varbinary(max) from the Postgres server?

I'm using BigSQL PostgreSQL 9.6, the latest ODBC driver for PostgreSQL 9.6, and the latest odbc_fdw.

FOR XML AUTO returns data containing this: (&\#x0; = null). Length of string is more or less correct, source geom is linestring.
MaxVarcharsize = -4:

Cannot get the current row value of column "[STREAM].geos" from OLE DB provider "STREAM" for linked server "(null)". [COLUMN_NAME= geos STATUS=Unknown DBBINDSTATUS]

MaxLongVarcharSize = 32k or 8k

(first "working character string is 4000 after it comes null values up to "real" length of text) example output below:
`LINESTRING(2.773577 3.849574,2.773598 ... ,2.603447 3.847�����������������������������������������������������������������������������ꪝ������⎮���
LINESTRING(2.905497 0.547610999999996

ODBC Problem

I think this is a function of the PostgreSQL ODBC driver

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL]
"MaxLongVarcharSize"="4094"


Changing that with regedit may work (no tabs or spaces around =). However, there may be other drawbacks.

This is also documented in the psqlODBC Configuration Options..


Max LongVarChar: The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value.

I believe setting this to SQL_NO_TOTAL (ie., -4) means that the ODBC driver has to malloc rather static allocate the col size.

GIS sub-optimal

From the PostGIS angle, you should be using Well-Known-Binary via ST_AsBinary and not storing the WKT as nvarchar, but instead storing the geometry from WKB in SQL Server.

StackExchange Database Administrators Q#164286, answer score: 2