I can't find the exact solution to this problem. I have a SQL script that creates another very long script in different steps.
What i do is, along the script, to add new pieces of script to a
varchar(max) using concatenation. The final script is so long that it's difficult for me to get it. I use the following as the final instruction:
SELECT [processing-instruction(x)] = @MyScript FOR XML PATH(''), TYPE;
In this way I can manage to get quite long results with this but sometimes the result is so long that it seems SSMS runs out of memory.
I tried saving my variable
@MyScript by selecting it and saving the result as a text or a file but it saves less than 20K characters. I have set the XML max output length as unlimited and it seems to work but when I click on the result cell with the blue content (the xml with the script) then SSMS freezes.
The nice thing is that APPEARENTLY the script is generated quite fast (I am logging with print the different steps) but I can't see the results of my efforts.
Is there some way i can get hold of the content of this lengthy