如何在SSMS中以文本形式检索超长varcharmax的值

Question

我找不到这个问题的确切解决方案。我有一个SQL脚本，它在不同的步骤中创建另一个非常长的脚本。

我所做的是，沿着脚本，使用连接将新的脚本片段添加到varchar(max)。最后的脚本太长了，我很难理解。我使用以下内容作为最后的指令：

SELECT [processing-instruction(x)] = @MyScript 
FOR XML PATH(''), TYPE;

通过这种方式，我可以设法获得相当长的结果，但有时结果太长，以至于SSMS似乎耗尽了内存。

我尝试通过选择变量@MyScript并将结果保存为文本或文件来保存它，但它节省的字符少于20K。我已经将XML的最大输出长度设置为无限制，似乎可以工作，但是当我单击带有蓝色内容的结果单元格(带有脚本的xml )时，SSMS就会冻结。

有趣的是，脚本的生成速度非常快(我正在使用print记录不同的步骤)，但是我看不到我的努力的结果。

有什么方法可以让我掌握这个冗长的varchar(max)的内容吗？

谢谢

Answer 1

创建一个选择变量作为输出的过程。

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

然后转到命令行并执行：

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

如果您希望使用T-SQL完成所有这些工作，那么可以使用xp_cmdshell运行bcp命令(如果您的服务器上启用了该命令)。

Answer 2

如果希望将MAX类型变量或字段的内容-- VARCHAR(MAX)、NVARCHAR(MAX)和VARBINARY(MAX) --保存到一个文件中，可以创建一个SQLCLR存储过程或函数(我会选择function，以便可以在查询中内联使用它来保存字段的内容，而不必首先将其传递给变量，更不用说它是基于集合的和全部的)。

为了保存字符串，您可能只需执行像File.WriteAllText这样简单的操作。大致是这样的：

C#代码：

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;
  }
}

请注意：

• 由于要访问文件系统，因此需要向PERMISSION_SET = EXTERNAL_ACCESS注册程序集。虽然将数据库设置为TRUSTWORTHY ON比较容易，但最好是：
  • 对程序集进行签名并使用密码来保护密钥文件。
  • 从DLL创建非对称密钥
  • 从该非对称密钥创建登录
  • 授予登录d19 permission

​

• 以上代码使用的是系统默认编码，该编码可能与字符串在SQL Server中的编码方式一致，也可能不一致。如有必要，还有另一个接受第三个输入参数以设置上面的encoding.
• the的File.WriteAllText重载。C#代码不测试.IsNull的任何一个输入参数，因为如果任何一个输入参数为NULL.

，则使用WITH RETURNS NULL ON NULL INPUT创建T-SQL包装器对象会更好，因为它会跳过完全调用此方法

测试查询：

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;

在我的慢速笔记本电脑上，它导出了23.8MB (25,000,000字节)：

CPU时间=94ms，运行时间=188ms。

并且，在REPLICATE函数中添加一个0，在以下位置导出238MB (250,000,000字节)：

CPU时间=1704ms，运行时间=8714ms。

(总运行时间为33秒，因此生成要保存的值需要24秒)

现在，如果您不想创建SQLCLR程序集和非对称密钥等，这个函数(名为File_WriteFile)和许多其他函数(包括File_WriteFileBinary)都可以在SQL#库(我是该库的作者)中找到。请注意，File_*功能仅在完整版中可用，在免费版中不可用。

Answer 3

避免SSMS处理大型变量的全部内容的另一种方法是让SQL Server将该变量的内容作为SQL脚本通过电子邮件发送给您。类似于：

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

请注意，默认的最大文件附件大小为1 MB。我不确定该限制是否适用于以文件形式附加的查询结果，因此您可能需要首先运行以下命令来增加该限制：

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

更多信息：

• sp_send_dbmail
• sysmail_configure_sp