SQL 消息 8152，级别 16，状态 14，行 XXX 字符串或二进制数据将被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26791178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Msg 8152, Level 16, State 14, Line XXX String or binary data would be truncate
提问by MADnoobie
Scenario: A large stored procedure is run thousands of time a day, 6 days a weeks every year. For this one specific situation I'm getting an error
场景：大型存储过程每天运行数千次，每年每周运行 6 天。对于这种特定情况，我收到一个错误
Msg 8152, Level 16, State 14, Line 114
String or binary data would be truncated
Msg 8152, Level 16, State 14, Line 114
字符串或二进制数据将被截断
This stored procedure is about 800 lines of code, with lots of parameters, lots of variables and lots of tables.
这个存储过程大约有 800 行代码，有很多参数、很多变量和很多表。
Go
@exampleParam varchar(30)
@exampleParam datetime
DECLARE @declaredvara VARCHAR(50);
DECLARE @declaredvarb VARCHAR(50); -- where the line error is according to the printout
DECLARE @declaredvarc VARCHAR(50);
DECLARE @declaredvard VARCHAR(50); -- where line error is adjusted for comments in front of GO
BEGIN TRANS
-- some calculations (700+ lines)
COMMIT TRANS
--error catch
ROLLBACK TRANS
Problem: I'm want to know if its possible to get an accurate Line error read, so I can at least narrow down which tables I have to check. There are tons of tables involved in this stored procedure.
问题：我想知道是否可以读取准确的 Line 错误，因此我至少可以缩小必须检查的表的范围。这个存储过程中涉及到大量的表。
*** Both declared variables(before and after adjustment of GO) it points to are dead weight, they're nvr used.
*** 它指向的两个声明变量（GO 调整前后）都是自重，它们是 nvr 使用的。
Bonus info: I ran the stored procedure with params populated and params = '' except for the datetimes. In both cases I got the same error. However, I know the stored procedure works because it's being used thousands of times a day w/out this error.
奖励信息：我运行了填充了 params 并且 params = '' 的存储过程，但日期时间除外。在这两种情况下，我都遇到了相同的错误。但是，我知道存储过程有效，因为它每天被使用数千次而没有出现此错误。
EXEC SP '','','','','2014-11-6'
EXEC SP 'XX_XX_XX','',1,'','2014-11-6'
--both return the same error of Msg 8152, Level 16, State 14
--XX is a specific serialnum.
--However all other serialnum have no problem
EDIT: The DB is running SQL Server 2005 EDIT2: I'm using SQL Server 2008 to edit. - So debug isn't an option
编辑：数据库正在运行 SQL Server 2005 EDIT2：我使用 SQL Server 2008 进行编辑。- 所以调试不是一个选项
回答by Sunny Sunny
BEGIN CATCH
DECLARE @body VARCHAR(4000)
SET @body='<b>Exception in spname </b><br/>'
SET @body=@body + ( SELECT
'<b>Error No :</b> '+ CONVERT(VARCHAR(100),ERROR_NUMBER()) +'<br/>'
+'<b>Error Severity :</b> '+CONVERT(VARCHAR(1000),ERROR_SEVERITY()) +'<br/>'
+'<b>Error State :</b> '+CONVERT(VARCHAR(1000),ERROR_STATE()) + '<br/>'
+'<b>Error Procedure :</b> '+CONVERT(VARCHAR(1000),ERROR_PROCEDURE())+'<br/>'
+'<b>Error Line :</b> '+CONVERT(VARCHAR(100),ERROR_LINE())+'<br/>'
+'<b>Error Message :</b> '+CONVERT(VARCHAR(2000),ERROR_MESSAGE()));
END CATCH
回答by Anees Rana
Used that it will fix Msg 8152, Level 16, State 14, Line 114 String or binary data would be truncated.
用于修复消息 8152，级别 16，状态 14，行 114 字符串或二进制数据将被截断。
SET ansi_warnings OFF
GO
回答by Vahid Farahmandian
This error indicates that you are going to store some thing bigger that it's storage. you may need to check if you are going to store some string with more that 50 characters in @declaredvara or @declaredvarb or @declaredvarc or @declaredvard or any other variables or table columns. for example you may define @test as varchar(2) and then try to insert 'vahid' into it. in this case similar exception will be thrown. There is 2 way(as I know) that you can find the error ocurance line:
这个错误表明你要存储一些比它的存储更大的东西。您可能需要检查是否要在 @declaredvara 或 @declaredvarb 或 @declaredvarc 或 @declaredvard 或任何其他变量或表列中存储超过 50 个字符的字符串。例如，您可以将 @test 定义为 varchar(2)，然后尝试将 'vahid' 插入其中。在这种情况下，将抛出类似的异常。有两种方法（据我所知）可以找到错误发生线：
- If you need to find the exact line of error occurrence you may need to debug you SP using sql server debug tools(Read more about debugging in Sql Server here)
- You can also use TRY...CATCH blocks to check which block of code in you SP causes the error.
- 如果您需要找到错误发生的确切行，您可能需要使用 sql server 调试工具调试您的 SP（在此处阅读有关在 Sql Server 中调试的更多信息）
- 您还可以使用 TRY...CATCH 块来检查 SP 中的哪个代码块导致错误。
回答by MADnoobie
It turns out one of the variables in the area was declared but never given a value.
事实证明，该区域中的一个变量已声明但从未赋值。
That variable was later used as a param in executing another stored procedure from within. Which resulted in a null param being passed into a param that couldn't contain it.
该变量后来被用作从内部执行另一个存储过程的参数。这导致一个空参数被传递到一个不能包含它的参数中。
Thanks everyone for the responses.
感谢大家的回应。
