3

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.

Improve this question
2
  • did anything work?
    – Evan Carroll
    Commented Feb 20, 2017 at 23:15
  • 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.
    – simpleuser008
    Commented Feb 21, 2017 at 13:10

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • 1
    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
    Commented Feb 15, 2017 at 7:24
  • 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.
    – simpleuser008
    Commented Feb 21, 2017 at 13:14
  • @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.
    – Evan Carroll
    Commented Feb 21, 2017 at 14:55
  • 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.
    – simpleuser008
    Commented Feb 22, 2017 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.