SQL Server字段被截断 [英] SQL Server field getting truncated
问题描述
好，我正在使用SQL Server 2008，并且具有类型为VARCHAR(MAX)的表字段.问题是，当使用Hibernate保存信息时，VARCHAR(MAX)字段的内容将被截断.在应用服务器或数据库服务器上都没有看到任何错误消息.
Ok I'm using SQL Server 2008 and have a table field of type VARCHAR(MAX). Problem is that when saving information using Hibernate, the contents of VARCHAR(MAX) field is getting truncated. I don't see any error messages on either the app server or database server.
此字段的内容只是纯文本文件.该文本文件的大小为383KB.
The content of this field is just a plain text file. The size of this text file is 383KB.
这是我迄今为止为解决此问题所做的事情:
This is what I have done so far to troubleshoot this problem:
-
将数据库字段从
VARCHAR(MAX)更改为TEXT，并且相同 问题发生了.
Changed the database field from
VARCHAR(MAX)to TEXT and same problem occurs.
使用了SQL Server Profiler，我注意到全文
内容正在
由数据库服务器接收，但由于某些原因，探查器在尝试时冻结
查看带有截断问题的SQL.就像我说的那样，在结冰之前，我
确实注意到正在接收全文文件内容(383KB)，所以看起来
可能是数据库问题.
Used the SQL Server Profiler and I noticed that the full text
content is being
received by the database server, but for some reason the profiler freezes when trying
to view the SQL with the truncation problem. Like I said, just before it freezes, I
did noticed that the full text file content (383KB) are being received, so it seems
that it might be the database problem.
以前有人遇到过此问题吗?有什么想法导致这种截断吗?
Has anyone encountered this problem before? Any ideas what causes this truncation?
注意:我只想提一下我要进入SQL Studio并复制TEXT字段内容并将其粘贴到Textpad.这就是我注意到它被截断的方式.
NOTE: just want to mention that I'm just going into SQL Studio and just copying the TEXT field content and pasting it to Textpad. That's how I noticed it's getting truncated.
谢谢.
推荐答案
您的问题是您认为Management Studio将向您提供所有数据.没有.转到工具>选项>查询结果> SQL Server.如果您正在使用将结果网格化"，请将非XML数据"更改为检索到的最大字符数"(请注意，结果网格化将消除任何CR/LF").如果您将结果用于文本，请更改每列中显示的最大字符数".
Your problem is that you think Management Studio is going to present you with all of the data. It doesn't. Go to Tools > Options > Query Results > SQL Server. If you are using Results to Grid, change "Maximum Characters Retrieved" for "Non XML data" (just note that Results to Grid will eliminate any CR/LF). If you are using Results to Text, change "Maximum number of characters displayed in each column."
您可能会想输入更多内容，但是在Management Studio中可以返回的最大值是:
You may be tempted to enter more, but the maximum you can return within Management Studio is:
65535 for Results to Grid
8192 for Results to Text
如果您真的想在Management Studio中查看所有数据，可以尝试将其转换为XML，但这也有问题.首先将结果网格">"XML数据"设置为5 MB或无限，然后执行以下操作:
If you really want to see all the data in Management Studio, you can try converting it to XML, but this has issues also. First set Results To Grid > XML data to 5 MB or unlimited, then do:
SELECT CONVERT(XML, column) FROM dbo.table WHERE...
现在，这将产生一个实际可以单击链接的网格结果.这将打开一个新的编辑器窗口(它不会是查询窗口，因此不会有执行按钮，IntelliSense等)，并将您的数据转换为XML.这意味着它将用>等替换>.这是一个简单的示例:
Now this will produce a grid result where the link is actually clickable. This will open a new editor window (it won't be a query window, so won't have execute buttons, IntelliSense, etc.) with your data converted to XML. This means it will replace > with > etc. Here's a quick example:
SELECT CONVERT(XML, 'bob > sally');
结果:
当您单击网格时，您会得到一个新窗口:
When you click on the grid, you get this new window:
(它确实具有IntelliSense，可以验证XML格式，这就是为什么看到弯曲的原因.)
(It does kind of have IntelliSense, validating XML format, which is why you see the squigglies.)
回到牧场
如果您只是想进行健全性检查，而又不想真的将所有383K复制到其他地方，那就不要！只需使用以下方法检查即可:
If you just want to sanity check and don't really want to copy all 383K elsewhere, then don't! Just check using:
SELECT DATALENGTH(column) FROM dbo.table WHERE...
这应该向您显示您的数据已被数据库捕获，而问题出在工具和验证方法上.
This should show you that your data was captured by the database, and the problem is the tool and your method of verification.
(此后，我写了 查看全文
