如何查看SQL Server 2008 Management Studio中的文本或varchar(MAX)列的完整内容?

In this live SQL Server 2008 (build 10.0.1600) database, there's an Events table, which contains a text column named Details. (Yes, I realize this should actually be a varchar(MAX) column, but whoever set this database up did not do it that way.)

在这个live SQL Server 2008 (build 10.0.1600)数据库中，有一个事件表，其中包含一个名为Details的文本列。(是的，我知道这实际上应该是一个varchar(MAX)列，但是设置这个数据库的人不是这样做的。)

This column contains very large logs of exceptions and associated JSON data that I'm trying to access through SQL Server Management Studio, but whenever I copy the results from the grid to a text editor, it truncates it at 43679 characters.

这一列包含非常大的异常日志和相关的JSON数据，我正试图通过SQL Server Management Studio访问这些数据，但每当我将结果从网格复制到文本编辑器时，它会将其截断为43679个字符。

I've read on various locations on the Internet that you can set your Maximum Characters Retrieved for XML Data in Tools > Options > Query Results > SQL Server > Results To Grid to Unlimited, and then perform a query such as this:

我已经在Internet上阅读了各种不同的位置，您可以在工具>选项中为XML数据获取最大的字符，>查询结果> SQL Server >的结果是无限的，然后执行如下的查询:

select Convert(xml, Details) from Events
where EventID = 13920

(Note that the data is column is not XML at all. CONVERTing the column to XML is merely a workaround I found from Googling that someone else has used to get around the limit SSMS has from retrieving data from a text or varchar(MAX) column.)

(注意，数据是列，根本不是XML。将列转换为XML仅仅是我从google上找到的一种解决方案，我在google上发现，其他一些人曾试图绕过SSMS从文本或varchar(MAX)列检索数据的限制。

However, after setting the option above, running the query, and clicking on the link in the result, I still get the following error:

但是，在设置了上面的选项，运行查询，点击结果中的链接后，我仍然得到以下错误:

Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 5, position 220160.

无法显示XML。发生以下错误:发生了意外的文件结束。第5行,220160位置。

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

一种解决方案是增加从服务器检索XML数据的字符数量。要更改此设置，请单击“工具”菜单上的“选项”。

So, any idea on how to access this data? Would converting the column to varchar(MAX) fix my woes?

那么，对于如何访问这些数据有什么想法吗?将列转换为varchar(MAX)会解决我的问题吗?

9 个解决方案

#1

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below.

在非常有限的情况下，一种可能有效的方法是简单地将列命名为如下所示的特殊方式。

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

In SSMS 2012 and 2014 this displays the results as below

在2012和2014 SSMS中，结果如下所示

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

单击它将在XML查看器中打开完整的结果。向右滚动显示B的最后一个字符被保留，

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

然而，这确实有一些重要的问题。向查询添加额外的列会破坏效果，而额外的行都与第一个列连接在一起。最后，如果字符串包含诸如 <打开xml查看器之类的字符，则会出现解析错误。< p>

A more robust way of doing this that avoids issues of SQL Server converting < to < etc or failing due to these characters is below (credit Adam Machanic here).

一种更健壮的方法，可以避免SQL Server将 <转换为<由于这些角色的原因而导致的失败(在这里是credit adam machanic)。< p>

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

#2

I was able to get this to work...

我能让这个工作…

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;

#3

One work-around is to right-click on the result set and select "Save Results As...". This exports it to a CSV file with the entire contents of the column. Not perfect but worked well enough for me.

一种方法是右键单击结果集并选择“将结果保存为…”。这将它导出到包含列的全部内容的CSV文件中。不完美，但对我来说足够好。

#4

The simplest workaround I found is to backup the table and view the script. To do this

我发现的最简单的解决方案是备份表并查看脚本。要做到这一点

1. Right click your database and choose Tasks > Generate Scripts...
2. 右键单击您的数据库并选择任务>生成脚本…
3. "Introduction" page click Next
4. “介绍”页面点击Next
5. "Choose Objects" page
   1. Choose the Select specific database objects and select your table.
   2. 选择特定的数据库对象并选择您的表。
   3. Click Next
   4. 单击Next
6. “选择对象”页面选择选择特定的数据库对象并选择您的表。单击Next
7. "Set Scripting Options" page
   1. Set the output type to Save scripts to a specific location
   2. 设置输出类型，将脚本保存到特定位置。
   3. Select Save to file and fill in the related options
   4. 选择Save to file并填写相关选项
   5. Click the Advanced button
   6. 单击高级按钮
   7. Set General > Types of data to script to Data only or Schema and Data and click ok
   8. 将一般的>数据类型设置为只用于数据或模式和数据的脚本，并单击ok。
   9. Click Next
   10. 单击Next
8. “设置脚本选项”页面设置输出类型，将脚本保存到特定位置，选择Save to file并填充相关选项，单击高级按钮，设置常规的>类型的数据以脚本保存到数据或模式和数据，然后单击ok单击Next
9. "Summary Page" click next
10. “总结页面单击next
11. Your sql script should be generated based on the options you set in 4.2. Open this file up and view your data.
12. 您的sql脚本应该基于您在4.2中设置的选项生成。打开这个文件并查看您的数据。

#5

The data type TEXT is old and should not be used anymore, it is a pain to select data out of a TEXT column.

数据类型文本是旧的，不应该再使用了，从文本列中选择数据是很痛苦的。

ntext, text, and image (Transact-SQL)

ntext、text和image (Transact-SQL)

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

在Microsoft SQL Server的未来版本中，将删除ntext、文本和图像数据类型。避免在新的开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序。使用nvarchar(max)、varchar(max)和varbinary(max)代替。

you need to use TEXTPTR (Transact-SQL) to retrieve the text data.

您需要使用TEXTPTR (Transact-SQL)检索文本数据。

Also see this article on Handling The Text Data Type.

另外，请参阅有关处理文本数据类型的文章。

#6

It sounds like the Xml may not be well formed. If that is the case, then you will not be able to cast it as Xml and given that, you are limited in how much text you can return in Management Studio. However, you could break up the text into smaller chunks like so:

听起来Xml可能格式不太好。如果是这种情况，那么您将无法将其转换为Xml，并且考虑到这一点，您只能在Management Studio中返回多少文本。但是，你可以把文本分成小块，比如:

With Tally As
    (
        Select ROW_NUMBER() OVER ( ORDER BY s1.object_id ) - 1 As Num
        From sys.sysobjects As s1
            Cross Join sys.sysobjects As s2
    )
Select Substring(T1.textCol, T2.Num * 8000 + 1, 8000)
From Table As T1
    Cross Join Tally As T2
Where T2.Num <= Ceiling(Len(T1.textCol) / 8000)
Order By T2.Num

You would then need to manually combine them again.

然后需要再次手动组合它们。

EDIT

编辑

It sounds like there are some characters in the text data that the Xml parser does not like. You could try converting those values to entities and then try the Convert(xml, data) trick. So something like:

这听起来像是Xml解析器不喜欢的文本数据中的一些字符。您可以尝试将这些值转换为实体，然后尝试转换(xml、数据)技巧。所以类似:

Update Table
Set Data = Replace(Cast(Data As varchar(max)),'<','&lt;')

(I needed to cast to varchar(max) because the replace function will not work on text columns. There should not be any reason you couldn't convert those text columns to varchar(max).)

(我需要强制转换到varchar(max)，因为替换函数不能在文本列上工作。你不应该有任何理由不能将这些文本列转换为varchar(max)。

#7

Did you try this simple solution? Only 2 clicks away!

你试过这个简单的方法吗?只有2点击!

At the query window,

在查询窗口,

1. set query options to "Results to Grid", run your query
2. 将查询选项设置为“结果到网格”，运行查询
3. Right click on the results tab at the grid corner, save results as any files
4. 右键单击网格角上的results选项卡，将结果保存为任何文件

You will get all the text you want to see in the file!!! I can see 130,556 characters for my result of a varchar(MAX) field

你会得到所有你想看到的文本在文件!!我可以在varchar(MAX)字段中看到130,556个字符

#8

You are out of luck, I think. THe problem is not a SQL level problem as all other answers seem to focus on, but simply one of the user interface. Management Studio is not meant to be a general purpose / generic data access interface. It is not there to be your interface, but your administrative area, and it has serious limitations handling binary data and large test data - because people using it within the specified usage profile will not run into this problem.

我认为你运气不好。问题不是所有其他答案都关注的SQL级别问题，而是用户界面中的一个。Management Studio并不打算是通用的/通用的数据访问接口。它不是您的接口，而是您的管理区域，并且处理二进制数据和大型测试数据有严重的限制——因为在指定的使用配置文件中使用它的人不会遇到这个问题。

Presenting large text data is simply not the planned usage.

显示大的文本数据并不是计划中的用法。

Your only choice would be a table valued function that takes the text input and cuts it rows for every line, so that Management Studio gets a list of rows, not a single row.

您唯一的选择是一个表值函数，该函数接受文本输入，并为每一行裁剪行，以便Management Studio获得一个行列表，而不是单个行。

#9

I prefer this simple XML hack which makes columns clickable in SSMS on a cell-by-cell basis. With this method, you can view your data quickly in SSMS’s tabular view and click on particular cells to see the full value when they are interesting. This is identical to the OP’s technique except that it avoids the XML errors.

我更喜欢这个简单的XML技巧，它使SSMS中的列可以按单元单击。使用此方法，您可以在SSMS的表格视图中快速查看数据，并单击特定的单元格，以查看感兴趣的完整值。这与OP的技术相同，只是避免了XML错误。

SELECT
     e.EventID
    ,CAST(REPLACE(REPLACE(e.Details, '&', '&amp;'), '<', '&lt;') AS XML) Details
FROM Events e
WHERE 1=1
AND e.EventID BETWEEN 13920 AND 13930
;