how to get the full resultset from SSMS

Solution 1:

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 <

Solution 2:

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.

Solution 3:

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.

Solution 4:

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.