STAsText() Character Limit?

Question

STAsText() is supposed to return nvarchar(max) which has a limit of 2^31-1 bytes. I have a table with a geography column "ccord" and I'm using .STAsText() to return the WKT as follows: SELECT coord.STAsText() FROM ... The WKT returned is truncated at 43678 characters (I know for a fact the actual WKT is much larger). The binary data itself is all there as evident by viewing the Spatial Data in SSMS.

Answer 1

Are you sure that the WKT value returned from STAsText() itself is being truncated? From your description, it sounds more like just the SSMS display of that value is truncated.

For example, what do you get when you do:

SELECT LEN(coord.STAsText()) FROM ...

?

I'd expect this to verify that the size of the WKT value returned by your query can be significantly larger than 43,768 characters - it's just that SSMS won't display that many (but then again, you wouldn't use SSMS to export a large amount of WKT anyway).

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

SSMS -> Query -> Query Options -> Execution -> General -> Specify max size of text and ntext data returned from a SELECT statement is set to 2147483647 bytes. You'd think it would actually do what it advertises!!! But indeed, SELECT LEN(coord.STAsText()) FROM ... returns the correct length of 264371 chars. Thanks.