After rebooting SQL Server 2005 Standard 9.0.3233, we have been experiencing the above error in some of our stored procedures which try to insert into a table variable from a specific column of a table. The base table has the column defined as varchar(10), but the table variable has the column being inserted into defined only as varchar(3). However, the SELECT statement only returns data with 3 or less characters.
重新启动SQL Server 2005 Standard 9.0.3233后，我们在一些存储过程中遇到了上述错误，这些存储过程尝试从表的特定列插入表变量。基表的列定义为varchar（10），但是表变量将列插入到仅定义为varchar（3）的列中。但是，SELECT语句仅返回3个或更少字符的数据。
We have not changed the data or the code base in any other way, and this is only happening on our production server. If I run the same query on a test server with the same SQL Server 2005 edition installed, but an older backup, the error does not occur. The same data is returned in both queries if the INSERT is removed, or the table variable column is extended to match the base table.
我们没有以任何其他方式更改数据或代码库，这只发生在我们的生产服务器上。如果我在安装了相同SQL Server 2005版本但是较旧的备份的测试服务器上运行相同的查询，则不会发生错误。如果删除INSERT，则会在两个查询中返回相同的数据，或者扩展表变量列以匹配基表。
What I have noticed is that the execution plan is different when the same query is run on the two servers. On the server where the query works, there is a computed scalar operation which takes the column and does an implicit conversion to varchar(3), before it is then outputted to the nested loop join operation.
我注意到，当在两台服务器上运行相同的查询时，执行计划是不同的。在查询工作的服务器上，有一个计算的标量操作，它接受列并进行varchar（3）的隐式转换，然后输出到嵌套循环连接操作。
On the server that returns an error, there is a hash join and table scan of the base table instead. I have already tried to rebuild indices and update statistics on all tables involved, including using fullscan, and with the same stat_stream as in the server that works, but I can't get the same plan back.
在返回错误的服务器上，存在基表的散列连接和表扫描。我已经尝试重建索引并更新所有涉及的表的统计信息，包括使用fullscan，并使用与服务器中相同的stat_stream，但我无法获得相同的计划。
For now we have fixed the few stored procedures which were broken by modifying the size of the table variable column, but I would like to know if there is a way to get the statistics and indices back so that they produce the same plans as before, in case there is more code out there which just hasn't executed yet.
现在我们修改了几个通过修改表变量列大小而破坏的存储过程，但我想知道是否有办法获取统计信息和索引，以便它们生成与以前相同的计划，如果还有更多的代码尚未执行。
1 个解决方案
#1
1
This is known behavior and probably has nothing to do with your reboot. Effectively what's happening is that the optimizer is re-ordering the logical elements of your query for performance reason, but this is resulting in the truncation-error check being done before the WHERE clause's filtering.
这是已知的行为，可能与您的重启无关。实际上正在发生的事情是，优化器出于性能原因重新排序查询的逻辑元素，但这导致在WHERE子句的过滤之前完成截断错误检查。
The recommended solution is to wrap the column expression that gets assigned to your VARCHAR(3) in a CASE.. that duplicates the length test in your WHERE clause. I know that sounds illogical, but it usually fixes the problem.
建议的解决方案是在CASE中包装分配给VARCHAR（3）的列表达式，该表达式复制WHERE子句中的长度测试。我知道这听起来不合逻辑，但它通常可以解决问题。
#1
1
This is known behavior and probably has nothing to do with your reboot. Effectively what's happening is that the optimizer is re-ordering the logical elements of your query for performance reason, but this is resulting in the truncation-error check being done before the WHERE clause's filtering.
这是已知的行为，可能与您的重启无关。实际上正在发生的事情是，优化器出于性能原因重新排序查询的逻辑元素，但这导致在WHERE子句的过滤之前完成截断错误检查。
The recommended solution is to wrap the column expression that gets assigned to your VARCHAR(3) in a CASE.. that duplicates the length test in your WHERE clause. I know that sounds illogical, but it usually fixes the problem.
建议的解决方案是在CASE中包装分配给VARCHAR（3）的列表达式，该表达式复制WHERE子句中的长度测试。我知道这听起来不合逻辑，但它通常可以解决问题。