SQL 错误：字符串或二进制数据将被截断
我正在一个名为 Telligent 的社区平台上进行集成。我正在使用名为 BlogML 的第 3 方插件将博客文章从 XML 文件（BlogML 格式）导入到我的本地 Telligent 站点中。 Telligent 平台在其 SDK 中附带了许多类，以便我可以通过编程方式添加内容，例如博客文章。例如，
myWeblogService.AddPost(myNewPostObject);
我使用的 BlogML 应用程序本质上是解析 XML 并创建博客文章对象，然后使用类似上面示例行的代码将它们添加到站点。大约 40 次后导入后，我收到一个 SQL 错误：
Exception Details: System.Data.SqlClient.SqlException:
String or binary data would be truncated.
The statement has been terminated.
我相信此错误意味着我试图将太多数据插入到具有最大大小限制的数据库字段中。不幸的是，我无法判断这是哪个领域的问题。我在执行导入时运行了 SQL Server Profiler，但我似乎无法看到错误发生在哪个存储过程上。是否有另一种方法可以使用探查器或其他工具来准确查看哪个存储过程，甚至是哪个字段导致了错误？是否还有其他提示可以获取有关具体查看位置的更多信息？
哦，第三方工具的乐趣......
I'm doing an integration on a community platform called Telligent. I'm using a 3rd-party add-on called BlogML to import blog posts from an XML file (in BlogML format) into my local Telligent site. The Telligent platform comes with many classes in their SDK so that I can programmatically add content, such as blog posts. E.g.
myWeblogService.AddPost(myNewPostObject);
The BlogML app I'm using essentially parses the XML and creates blog post objects then adds them to the site using code like the above sample line. After about 40 post imports I get a SQL error:
Exception Details: System.Data.SqlClient.SqlException:
String or binary data would be truncated.
The statement has been terminated.
I believe this error means that I'm trying to insert too much data into a db field that has a max size limit. Unfortunately, I cannot tell which field this is an issue for. I ran the SQL Server Profiler while doing the import but I cannot seem to see what stored procedure the error is occurring on. Is there another way to use the profiler or another tool to see exactly what stored procedure and even what field the error is being caused by? Are there any other tips to get more information about where specifically to look?
Oh the joys of 3rd-party tools...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是正确的，因为异常是由于尝试将太多数据填充到基于字符/二进制的字段中。如果您捕获正确的事件，运行跟踪绝对应该允许您查看哪个过程/语句抛出异常，您想要捕获的事件包括：
如果您确定它是一个包含错误代码的存储过程，则可以取消捕获 # 的 1-4。确保您还捕获了跟踪中的所有关联列（如果您使用 Profiler 工具运行跟踪，则应该是默认列）。 Exception 类将在跟踪中包含实际错误，这应该允许您查看引发异常的同一 SPID 中紧邻的前面的语句。除了已完成事件之外，您还必须包含开始事件，因为发生的异常将阻止在跟踪中触发关联的已完成事件。
如果您可以将跟踪过滤到特定的数据库、应用程序、主机名等，那么如果您位于繁忙的服务器上，那么肯定会更容易调试，但是如果您位于空闲的服务器上，您可能不需要担心过滤。
假设您使用的是 Sql 2005+，跟踪将包括一个名为“EventSequence”的列，它基本上是一个按事件触发顺序排序的递增值。运行跟踪并捕获输出后，找到触发的“异常”事件（如果您使用分析器，该行将呈红色），那么您应该能够简单地找到最新的 SP:StmtStarting 或异常之前发生的同一 SPID 的 SQL:StmtStarting 事件。
这是我捕获的配置文件的屏幕截图，重现了与您类似的事件：
您可以看到红色的异常行，突出显示的行是在同一 SPID 的异常之前触发的前一个 SP:StmtStarting 事件。如果要查找该语句属于哪个存储过程，请查找 ObjectName 和/或 ObjectId 列中的值。
You are correct in that the exception is due to trying to stuff too much data into a character/binary based field. Running a trace should definitely allow you to see which procedure/statement is throwing the exception if you are capturing the correct events, those you'd want to capture would include:
If you know for certain it is a stored procedure that includes the faulty code, you could do away with capturing #'s 1-4. Be sure you capture all associated columns in the trace as well (should be the default if you are running a trace using the Profiler tool). The Exception class will include the actual error in your trace, which should allow you to see the immediate preceding statement within the same SPID that threw the exception. You must include the starting events in addition to the completed events as an exception that occurs will preclude the associated completed events from firing in the trace.
If you can filter your trace to a particular database, application, host name, etc. that will certainly make it easier to debug if you are on a busy server, however if you are on an idle server you may not need to bother with the filtering.
Assuming you are using Sql 2005+, the trace will include a column called 'EventSequence', which is basically an incrementing value ordered by the sequence that events fire. Once you run the trace and capture the output, find the 'Exception' event that fired (if you are using profiler, the row's it will be in Red color), then you should be able to simply find the most recent SP:StmtStarting or SQL:StmtStarting event for the same SPID that occurred before the Exception.
Here is a screen shot of a profile I captured reproducing an event similar to yours:
You can see the exception line in Red, and the line highlighted is the immediate preceding SP:StmtStarting event that fired prior to the exception for the same SPID. If you want to find what stored procedure this statement is a part of, look for the values in the ObjectName and/or ObjectId columns.
如果犯了一些愚蠢的错误，你就会得到这个错误。
如果您尝试插入类似的字符串。
这里 /n 是罪魁祸首。从字符串中删除 /n 以摆脱此错误。
我希望这会对某人有所帮助。
By doing some silly mistakes you will get this error.
if you are trying to insert a string like.
here /n is the culprit.Remove /n from the string to get out of this error.
I hope this will help some one.