How to retrieve as a text the value of a very long varcharmax in SSMS

Question

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 varchar(max) ?

Thanks

Answer 1

Create a procedure that selects the variable as output.

SELECT @MyScript XmlData
FOR XML PATH(''), TYPE;

Then go to the command line and execute:

bcp "exec sp_OutputXml" queryout MyScript.xml -w -T -S <server> -d <database>

If you wanted to do it all with T-SQL you could run the bcp command with xp_cmdshell if you have that enabled on your server.

Answer 2

If you want to save the contents of a MAX type variable or field -- VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) -- to a file, you can create a SQLCLR stored procedure or function ( I would choose function so that it can be used inline in a query to saved the contents of a field without first transferring it to a variable, not to mention being set-based and all ).

For saving a string, you can probably get away with doing something as simple as File.WriteAllText. Something along the lines of:

C# code:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public class SaveStuff
{
  [SqlFunction(IsDeterministic = false, IsPrecise = true)]
  public static SqlInt32 SaveToFile([SqlFacet(MaxSize = 500)] SqlString FilePath,
      [SqlFacet(MaxSize = -1)] SqlString TextToSave)
  {
      File.WriteAllText(FilePath.Value, TextToSave.Value);

      return TextToSave.Value.Length;
  }
}

Please note:

• due to accessing the file system, the assembly will need to be registered with PERMISSION_SET = EXTERNAL_ACCESS. While it is easier to set the database to TRUSTWORTHY ON, it is far better to:
  • sign the assembly and use a password to protect the key file.
  • create an Asymmetric Key from the DLL
  • create a Login from that Asymmetric Key
  • grant the Login the EXTERNAL ACCESS ASSEMBLY permission
• the code above is using the system default encoding, which may or may not match how the string is encoded within SQL Server. If necessary, there is another overload of File.WriteAllText that accepts a 3rd input parameter to set the encoding.
• the above C# code does not test either input parameter for .IsNull as it is better to create the T-SQL wrapper object using WITH RETURNS NULL ON NULL INPUT as it skips calling this method entirely if either input param is NULL.

Test query:

DECLARE @Bob NVARCHAR(MAX);
SET @Bob = REPLICATE(CONVERT(NVARCHAR(MAX), N'This is just a test, yo!  '), 1000000);

SELECT LEN(@Bob); -- 25,000,000


SET STATISTICS TIME ON;

SELECT dbo.SaveToFile(N'C:\TEMP\StackOverflow\z.txt', @Bob); -- 25,000,000

SET STATISTICS TIME OFF;

On my slow laptop, it exported 23.8 MB (25,000,000 bytes) in:

CPU time = 94 ms, elapsed time = 188 ms.

and, adding a 0 to the REPLICATE function, exported 238 MB (250,000,000 bytes) in:

CPU time = 1704 ms, elapsed time = 8714 ms.

(total elapsed time was 33 seconds, so it took 24 seconds to generate the value to be saved)

---

Now, if you don't want to mess with creating the SQLCLR assembly and the Asymmetric Key, etc., this function (named File_WriteFile), and many others (including File_WriteFileBinary), are available in the SQL# library (which I am the author of). Please note that the File_* functions are only available in the Full version and not in the Free version.

Answer 3

Another option that avoids SSMS from dealing with the full contents of the large variable is having SQL Server email you the contents of that variable as a SQL script. Something like:

sp_send_dbmail @query = 'SELECT @MyScript AS [--MyScript]',
               @recipients = '[email protected]',
               @subject = 'My SQL Script',
               @attach_query_result_as_file = 1,
               @query_attachment_filename = 'DoLotsOfThings.sql',
               @query_no_truncate = 1;

Please note that the default maximum file attachment size is 1 MB. I am not sure if that limitation applies to query results that are attached as files, so you might need to first run the following to increase that limit:

sysmail_configure_sp 'MaxFileSize', '52428800'; -- set Max to 50 MB

More info:

• sp_send_dbmail
• sysmail_configure_sp