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

Question

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,2.905621 0.547591999999995,...,2.894462 0.5�����������������������������������������������������������������������������竿������ᔺ���
LINESTRING(3.608147 2.496786,3.607717 2.496774,3.607176 2.496804,3.605301 2.497387,3.603758 2.49786999999999,3.602421 2.49828599999999,3.600669 2.49880499999999,3.59832 2.49952,3.596655 2.500024,3.594704 2.500643,3.592416 2.50134899999999,3.590898 2.50182599999999,3.590145 2.50205199999999,3.589244 2.502335,3.588036 2.502623,3.586846 2.50291299999999,3.584997 2.503305,3.583173 2.503676,3.581646 2.503925,3.580145 2.50402699999999,3.578866 2.504097,3.576313 2.504255,3.574554 2.504361,3.572587 2.50447699999999,3.57084 2.504583,3.568731 2.504699,3.566653 2.50480999999999,3.565399 2.504848,3.564418 2.50494399999999,3.562436 2.50506,3.561114 2.505138,3.559385 2.50509199999999,3.558475 2.50496699999999,3.5580

Next thing to try is to change driver from 9.06.01.00 to something else.

• Evan Carroll: did anything work?
• simpleuser008: yes , instead of returning id int , wkt(nvarchar(max) i did procedure in postgres to split over 4000 chars long text fields to max 4000 char long. Now it returns (id int orderid int ,wktpart nvarchar(4000) ) to sql server's temp table, which i parse together with" select r.id,geometry::STGeomFromText((SELECT t.geo as [text()] from #t t where t.id = r.id order by t.id, t.orderid for xml path('')),4326) as geom from #t r group by r.id ; , with clustered index in temp table it is 2 min for 150k rows (without it is 15 min). it fast enough for now.

Answer 1

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.

• simpleuser008: Well, reason why i use wkt is that i did not get varbinary(max) <-> bytea to work, but i have to see it again. Using SQL server is GIS sub-optimal but you cannot always select your stack.
• simpleuser008: There is no value for MaxlongVarcharSize which works. i tried 4k/8k/16k/32k (varchar(max) value is always corrupted after 4000 chars) , -4 gave me error and did not work at all. so , at the end i went around the problem.
• Evan Carroll: @simplexio Set it back to -4 and disable prefetch if possible: connect.microsoft.com/SQLServer/feedback/details/615000/… if that doesn't work. Try casting geos::varchar(8000) or whatever.
• simpleuser008: i have to test it when i have more time, splitting output to 4000 chars in postgresql and parsing it together in sql server is good enough solution for now.