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.

  • 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.
    – Hannah Vernon
    Mar 28, 2013 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, 2013 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, 2013 at 15:41
  • 1
    @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, 2013 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. Mar 28, 2013 at 15:52

2 Answers 2


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?

  • 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, 2013 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, 2013 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, 2013 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, 2013 at 13:24

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.

In SQL Server 2019, we'll get a new error message that will indicate the first table, column, and value that would have been truncated. See my answer here:

  • Your answer hinted me in the right direction. Thank you. I wish you could expend your answer with your knowledge about "stuffing too much data in #temp table". Oct 3, 2018 at 16:25
  • @AlexL It's not overly complex; you create a #temp table with a varchar(50) column but the data has 255 characters. This error is going to happen every time you try that. Oct 3, 2018 at 16:29
  • In my case I had an update to an INT column that caused this exception. So, it's not the column's data that was getting truncated, but, apparently, #temp table's "length of available space for data" itself was getting truncated, as far as I understand. Oct 3, 2018 at 16:32
  • @AlexL You might have seen an overflow for an int column, but not a string or binary data would be truncated error, unless that was happening as a result of the update (e.g. in a trigger or computed column). Would be interested in seeing your repro, there is certainly an explanation. Oct 3, 2018 at 17:04
  • "You might have seen an overflow for an int column" I literally set INT column to 1, that's it, not sure how can there be overflow. "but not a string or binary data would be truncated error" that is exactly what the error message said. "unless that was happening as a result of the update" well, I did update like so: UPDATE #t SET v=1 #FROM #t LEFT JOIN ... ON ... WHERE ..., so I UPDATEd an INT column, but there are no triggers and it's not a computed column (I manually set the value if LEFT JOINs and WHERE conditions are satisfied). Oct 3, 2018 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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