0

I am using: 

FOR JSON PATH

to transform my sql result set into JSON this works fine. How can I write the complete text to the file system (I am using SSMS and chose query -> results to -> results to file)? Currently, data is cutoff. I guess there is some property I can set in sql server management studio? Thanks.

cs0815
  • 16,751
  • 45
  • 136
  • 299
  • What do you mean there is a "cutoff"? Are you copy and pasting it from SSMS, where there is a limit to the about of characters displayed in a cell (about 65,000~ if you change the default setting)? If you want to export the data, you should be looking at an ETL process, or `bcp`, `sqlcmd` or something. – Thom A Dec 05 '19 at 11:01
  • cutoff as of when I write results to file system rather than grid. I am not copying from grid ... – cs0815 Dec 05 '19 at 11:03
  • So how are you writing them to the file system? Explain that in your question. We can't explain why the data is being "cut off" if you don't tell us what it is you're doing, – Thom A Dec 05 '19 at 11:05
  • chose query -> results to -> results to file - made this clearer mind you I thought that's obvious as I purely use SSMS – cs0815 Dec 05 '19 at 11:09
  • *"mind you I thought that's obvious as I purely use SSMS"* Considering that I described 1 alternative you're not using, and I can think of a couple of others, no it wasn't clear. But actually, my point stands, if you want to export the data you need to use something else. SSMS is a management tool, not an ETL tool. Export to File will truncate individual values just like results to Grid do. – Thom A Dec 05 '19 at 11:13
  • this is just a one of export and ssms should work for this. I remember that I had to adjust something in the properties to export large texts in columns. I would assume there is a similar for this use case. – cs0815 Dec 05 '19 at 11:15
  • 1
    There is the setting in `Options`->`Tools`->`Query Results`->`SQL Server`->`Results to Grid`->`Non XML Data` to change the total length returned (maximum value being 65535), but if you need much larger values, then what I state in the comment is still true. – Thom A Dec 05 '19 at 11:18

1 Answers1

2

I hope you are asking below

As stated here

I also use XML but a slightly different method that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id) 
FROM sys.all_objects 
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')

PRINT @VeryLongText /*WILL be truncated*/

Make sure that the "XML data" limit in SSMS is set sufficiently high!

Screenshot

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115