This week Luvy and I encountered an interesting issue with SQL Server. We were working on a project that we ported over to SQL Server 2008. As part of the work we were working on a scheduled stored procedure that selects into a temporary table and then does a fairly standard cross server bulk update.
The surprise came when we tried to run the procedure and kept encountering a strange error neither of us had ever seen before, and that was completely non-descriptive. Specifically it was:
Warning: Fatal error 682 occurred at July 15 2011 11:01AM. Note the error and time, and contact your system administrator.
When we opened the SQL Server Logs we saw similarly vague logs stating:
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
Since we were working with a large procedure it took a while to identify the bulk update statement as the culprit. Then as we ran various tests of the statement, each time scaling down the scope of what it was doing, we became more and more confused as to why the update statement would not work. Strangely enough the statement seemed to work when run from SQL Server 2005 to the SQL Server 2000 table.
Naturally from the error message both of us thought the SQL Server 2008 server itself might be corrupt. Looking for help online did not provide us with any useful suggestions for how to deal with this. However when we ran DBCC CHECKDB against the database everything looked fine.
We submitted the question on Twitter and a helpful user @banerjeeamit indicated that the problem was due to indexes we had on the tables we were connecting.
Sure enough when we rebuilt the indexes on the SQL Server 2000 table and added a non-clustered index to the temporary SQL Server 2008 table the Bulk update statement ran without any problems!
I thought this was an interesting issue and definitely was not clearly covered online. I hope our findings in fixing this issue will help someone experiencing similar isses.