After rebooting SQL Server 2005 Standard 9.0.3233, we encountered the aforementioned error in some of our stored procedures that try to insert into a table variable from a specific column of the table. The base table has a column defined as varchar (10), but the table variable has an inserted column defined only as varchar (3). However, the SELECT statement returns only data with 3 or less characters.
We did not change the data or the code base in any other way, and this only happens on our production server. If I run the same query on a test server with the same release of SQL Server 2005, but with an older backup, the error does not occur. The same data is returned in both queries if the INSERT is deleted or the column of the table variable is expanded to fit the base table.
I noticed that the execution plan is different when the same request is executed on two servers. On the server on which the query is executed, there is a computed scalar operation that takes a column and performs an implicit conversion to varchar (3) before it is output to a nested loop operation.
On the server that returns the error, a hash join and table check on the base table are performed instead. I already tried to rebuild the indexes and update statistics for all the tables involved, including using full-screen mode and with the same stat_stream as the server that works, but I can’t get the same plan back.
Currently, we have recorded several stored procedures that were damaged by changing the size of a column of a table variable, but I would like to know if there is a way to return statistics and indexes so that they produce the same plans as before, in case there is another code that has not yet been executed.
source
share