How can I diagnose the source of a very inconsistent, un-reproducable 'String or Binary data would be truncated.' error, when I am quite positive the problem isn't related to user data being to large to fit into SQL objects?
I have a project that is a C# application which uses SQL Server Express 2005 as a local db engine. The database is used to mostly cache data and very little processing or transforming is done within the local database.
The exception to this is when I need to prepare locally cached data to use when a user prints a report from their application. In this case, the user calls a stored procedure and passes in a few variables. The stored procedure starts by deleting all data associated with the document being printed. In other words, it fully cleans the data cache before it begins.
Next, it goes through a very long and complex process of consolidating and building information. Much of this information is stored in temp tables and after the process is finished, it copies the temp information into the report data tables.
The problem: This application has existed for nearly 6 years. About once every few months, since the inception of the application, a random user has called me telling me that they have received a 'String or Binary data would be truncated.' error. Furthermore, their application has historically worked perfectly, some times for years. Then, all of a sudden, they can't print from their application and they receive the above message from SQL Server.
After receiving the error, they can't recover from it. In fact, every document they try to load (even one's that have worked minutes before the message appeared) are now impossible to generate because SQL Server keeps throwing this error.
I know and understand the nature of this error-- it's telling me that some data is larger than a specific field in which it's being inserted. However, this doesn't make since in my context for two, main reasons. Reason #1, I've never been able to reproduce this bug on any PC, but it is 100% consistent on the user's PC once it starts. Reason #2, across the years I've never been able to force this error to occur, regardless of the inputs I've stored in my associated data tables or passed to the stored procedure as parameters.
The only way I've been able to fix this problem is to flag the user account to delete their local database. Once the user deletes their db, they rebuild and sync it with the server and everything works perfectly again. The failing stored procedure also has a ludicrous amount of data checks and washing routines, all because of this dumb error. I've never been able to isolate the problem nor solve it.
I know that this type of question typically begs for the posting of code but I can't provide that. This is a BIG stored procedure and it contains some propitiatory details. Still, if anyone is aware of a SQL bug that could be caching something within the internals of SQL Server and produces this generic exception, I'd appreciate the information or your expert advice of where else to look for a solution.
Please ask questions. I will comment and update my post as needed. This annoyance has gone on long enough.