locked
Results from STAsText() being truncated RRS feed

  • Question

  • We are working we a vendor that wants WKT as input to their system.  The spatial datasets are polygonal so some of the output is larger than the output buffer in the results tab can hold.  Is there a way to increase this or redirect it into a file?
    Monday, January 26, 2015 7:24 PM

Answers

  • Here is what finally worked for me  that I picked up from someone's blog  http://ejvyas.blogspot.com/2010/10/ssms-can-not-paste-more-than-43679.html

    In Management Studio right click on the cell in the results window and use "Save result as" to export data to a text file.  I was able to export the entire 2 million plus string to a file.  I was able to paste that back into Management Studio using it with  geometry::STGeomFromText to recreate the shape in SQL.

    Thursday, January 29, 2015 12:06 AM

All replies

  • I think this refers to SQL Server Management Studio.

    You can save a query output to a file in SSMS by choosing Query Menu/Results To/File. However, a better discussion of alternatives exists here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3513cdf0-8096-46e7-96a6-0c404d20bdeb/can-i-print-to-file-using-t-sql?forum=transactsql. Besides these possibilities you can script an entire table or view as a series of SQL INSERT statements using the SMO scripter (in SSMS Object Explorer this is: Right-click database, choose Generate Scripts and choose the table you want. On the last page of the dialog there will be an Advanced tab. Click it, and change "type of data to script" to "Data only".

    You can influence the maximum size of output columns in SSMS by using Tools Menu/Options/Query Options. This option is limited to 8192 characters (results to text) or 65535 characters (results to grid). There is a workaround when you use grid output in that the maximum column size of XML data type can be bigger, up to "Unlimited". You'd have to cast your column to XML using something like this: "select CAST(shapegeog.STAsText() AS XML) from census". This doesn't add any XML tags, just renders an XML data type instead of text. Bear in mind that using large grids with SSMS will close your query down considerably and may even run SSMS out of memory. 

    Hope this helps, Bob

    Monday, January 26, 2015 9:11 PM
  • It didn't work writing it to a file in SSMS.  All I got was a tiny fraction of the X Y pairs.  There should be 10s of thousands of xy pairs and closing params. 

    Here is my SQL statement and all that it wrote out to a file. 

    SELECT geometry::UnionAggregate(shape).STAsText()

    FROM [dbo].[WLAU_INDEX]

    WHERE wla_name in ('chelan')



    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    MULTIPOLYGON (((-13347935.2834 6117264.6110000014, -13347927.375599999 6118468.8675, -13347924.41 6118920.5073999986, -13348165.235100001 6118915.1622999981, -13348271.0463 6119070.2285000011, -13348529.5865 6119069.6031000018, -13348533.053100001 6118468.

    (1 row(s) affected)

    Monday, January 26, 2015 10:41 PM
  • Without counting characters, it appears that the "results to file" option of SSMS uses the same maximum specified for "results to text".  

    How about the multiple other suggestions included in the posting and the URL pointer? Any of them useful?

    Bob

    Wednesday, January 28, 2015 11:41 PM
  • Here is what finally worked for me  that I picked up from someone's blog  http://ejvyas.blogspot.com/2010/10/ssms-can-not-paste-more-than-43679.html

    In Management Studio right click on the cell in the results window and use "Save result as" to export data to a text file.  I was able to export the entire 2 million plus string to a file.  I was able to paste that back into Management Studio using it with  geometry::STGeomFromText to recreate the shape in SQL.

    Thursday, January 29, 2015 12:06 AM