how to get the full resultset from SSMS

Question

How do you get back the full content of rows in SQL Server Management Studio?

If you use "results to grid" the data is encoded, so things like line breaks are lost. If you do "result to file" or "results to text" the text is limited to 8192 characters.

NOTE: I have the solution, but I am using SO to document this. If you have a better answer feel free to post it.

Possible duplicate of [How do you view ALL text from an ntext or nvarchar(max) in SSMS?](https://stackoverflow.com/questions/11897950/how-do-you-view-all-text-from-an-ntext-or-nvarcharmax-in-ssms) — Michael Freidgeim, Oct 12 '19 at 04:50

score 51 · Answer 1 · answered Jan 19 '12 at 17:02

51

I cast it to XML

select @variable_with_long_text
 as [processing-instruction(x)] FOR XML PATH

The processing-instruction bit is there to stop it entitising characters such as < to <

answered Jan 19 '12 at 17:02

Martin Smith

438,706
87
741
845

Simply casting it to xML will give you all of the data? Interesting. – javamonkey79 Jan 19 '12 at 18:00
1

In order for this to work I first had to select my column data into a variable and then do the XML conversion as described. – danjuggler Jan 04 '17 at 22:44
can this be made into a clever function or something that I can remember – Simon_Weaver Jun 22 '17 at 18:18

score 8 · Answer 2 · answered Nov 21 '17 at 16:03

8

Just use the default "Results to grid, then right click on the grid results and select "Save results as..." CSV.

The full content will be saved in the file, even with line breaks. Some varchar(max) columns had content of about 3MB and it was saved OK, not truncated.

answered Nov 21 '17 at 16:03

Vlad S.

438
6
7

This worked for me while trying to inspect large json strings in a column. +1 – Yogi May 17 '19 at 14:06

score 5 · Answer 3 · answered Jun 20 '12 at 00:24

5

I develop an add-in for SSMS - "SSMSBoost" and have recently added "Copy cell contents 1:1" feature (accessible in context menue of Grid). It will return you all data from the cell without any modifications and without truncating.

answered Jun 20 '12 at 00:24

Andrei Rantsevich

2,879
20
25

score 2 · Answer 4 · answered Jul 13 '18 at 07:57

I had never had this problem before changing to SSMS17, so I write here now.

In SSMS 17: select the table, right-click and select "modify first # rows", filter as you need, then select the cell in the modifiable grid: it will appear blank for long contents, press CTRL-A, then CTRL-C, and then past wherever you want.

score -1 · Answer 5 · answered Jan 19 '12 at 17:00

One of the DBA's at my company suggested a possible solution is to put the data in a temporary variable and then use the print function in a loop, like so:

DECLARE @contents varchar(MAX)
SET @contents = ''

SELECT     @contents = @contents + Contents + CHAR(13)
FROM         dbo.tFOO
WHERE     someConition

DECLARE @tContents TABLE (id int IDENTITY, contents varchar(MAX))

WHILE @contents LIKE '%' + CHAR(13) + '%'
BEGIN
    PRINT SUBSTRING(@contents, 0, CHARINDEX(CHAR(13), @contents))

    SET @contents = SUBSTRING(@contents, CHARINDEX(CHAR(13), @contents)+1, LEN(@contents) - CHARINDEX(CHAR(13), @contents))
END

how to get the full resultset from SSMS

5 Answers5

Linked