Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

TL;DR

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.

share|improve this question
    
You could run a trace using SQL Server Profiler on the server that is throwing the error, and wait for the error to occur. SQL Server Profiler can be configured to show you the SQL text of the command that threw the error. –  Max Vernon Mar 28 '13 at 15:11
2  
The first thing I would check for if this is on a LOCAL user copy of your database is that none of the data definitions have been changed from what they should be. –  JNK Mar 28 '13 at 15:21
    
There is no way the definitions could change. My application is 100% responsible for the management of the database, and I haven't updated the structure or any associated stored procedures in years. Also, profiler hasn't been helpful in the past. I never could get anything useful from it, even when I installed it on a user's PC and tried to analyze the situation. It's also worth noting that this isn't a "copy" of a server database. This is a simple(-ish) DB that caches data and has a couple stored procedures for building tables for printed documents. That's all. –  RLH Mar 28 '13 at 15:41
    
@RLH "there is no way" means you haven't checked :) If it's a user database on a user system it's always possible something is changing a table definition. Honestly that is the first thing I would check. If it happened recently it may be in the default trace if there is one. –  JNK Mar 28 '13 at 15:47
1  
@Max I would suggest a server-side trace. There is no reason to use Profiler which is known to cause performance problems even with very restrictive traces (filters can be a double-edged sword). Most of the overhead in Profiler is actually in the UI. Server-side trace is much safer. –  Aaron Bertrand Mar 28 '13 at 15:52
show 5 more comments

2 Answers

I had similar error using different databases. All these errors were related to storing unicode varchar into non unicode varchar field. The problem was not displaying always, but it happened only when the text used contained multi byte characters.

So, first of all: does you database uses multi byte NVARCHAR instead of old style VARCHAR? And, second, are you really really really sure that your client cannot input text transmitted to your database server as multi byte characters?

share|improve this answer
    
you are possibly closest to an actual answer. I created the initial db structure years ago, using varchars. Still don't know why this would be a problem when a user can rebuild the db, and relaunch the process with the same inputs and it "magically" start working. Still, I've come to hate varchars because they've caused more grief than they are worth. Now, I strictly stick with nvarchars, even though they are "2x" the size of vrachars. –  RLH Mar 28 '13 at 16:22
    
I don't know if you use different connections for every logged in user. If this is the case, of if you may manage to do something similar in your environment, then create a LOGIN TRIGGER, check for the user being connected, and then activate some sort of logging or profiling. This way you might get the information you are loooking for. Otherwise I really cannot find any solution at database level: you really need to work at application level, checking data sent to database. –  eppesuig Mar 29 '13 at 8:13
    
Just out of curiosity: do you still get the error if you SET ANSI_WARNINGS OFF and let the database truncate your data as explained in msdn.microsoft.com/en-US/library/ms190368%28v=sql.90%29.aspx –  eppesuig Mar 29 '13 at 8:23
    
I seem to recall that I tried this once a long time ago. Either I couldn't get it to work in the SP or, no, it didn't work. –  RLH Mar 29 '13 at 13:24
add comment

SQL Server does not make this error up for fun. It can come from trying to stuff too much data into a #temp table, for example. I haven't read your whole novel but Max has it right: you can trace for this error and then collect the statement / procedure that caused it. This should help you narrow it down.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.