2

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.

Tom V
  • 15,670
  • 7
  • 63
  • 86
RLH
  • 927
  • 1
  • 8
  • 16
  • 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 '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
  • 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 '13 at 15:47
  • @RLH This is reinforced for me by your fix - rebuilding the user database. – 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
  • @JNK, fair enough. But how could that happen? Firstly, nothing plays around with the DBs, other than my thin-client application. Even those updates are delegated from the server and I haven't pushed out a db update in, oh... 2 years? Furthermore, these updates when applied, come from SQL Scripts that are downloaded and execvd against the user's DB. I mention this because if anything is changing these databases, it has to becoming from me. Or something "magical" is going on that I am oblivious to. – RLH Mar 28 '13 at 16:01
  • @RLH well since it only happens with one client/user in particular I would say something in their environment - another application or a virus or a smart-aleck nephew who likes to play around with databases – JNK Mar 28 '13 at 16:27
  • @JNK, I appologize that I must have been unclear. This happens randomly to different users. My user base is about 200 users. I've had, oh... 20 people call me on this problem in 6 years. Each have the exact same issue and when I rebuild their DBs it goes away, often to never return. – RLH Mar 28 '13 at 16:43
  • @RLH ah ok that is different, then. From my initial reading it sounded like the same customer has this recurring issue. I used to work helpdesk so my first thought is it's environmental – JNK Mar 28 '13 at 17:00
  • Similar issue happened to me (hence, why I am here). I my case a very simple UPDATE #tempTable SET value = 1 FROM #tempTable caused the same truncation issue. SSMS 2017. – Vanity Slug - codidact.com Oct 02 '18 at 18:23
  • @AlexL As OP of this question, I hope you find your answer. This is an old question, I no longer work for the company that had this issue and, to my knowledge, I never fixed this error. Feel free to edit this question and post any simpler code examples. I'm not sure, but you might be able to edit this question, with pending approval. Since I posted the question, I should be able to approve the edit... I think, so give it a try if you want. – RLH Oct 02 '18 at 18:46

2 Answers2

3

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?

eppesuig
  • 5,002
  • 1
  • 15
  • 14
  • 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 08: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 http://msdn.microsoft.com/en-US/library/ms190368%28v=sql.90%29.aspx – eppesuig Mar 29 '13 at 08: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
1

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:

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • 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". – Vanity Slug - codidact.com Oct 03 '18 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. – Aaron Bertrand Oct 03 '18 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. – Vanity Slug - codidact.com Oct 03 '18 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. – Aaron Bertrand Oct 03 '18 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). – Vanity Slug - codidact.com Oct 03 '18 at 17:16
  • Simply commenting out UPDATE #t SET v=1 FROM #t part (as well as LEFT JOINs and WHEREs which follow, of course) makes the error go away. I am not joking. (Not sure if you noticed, I wrote an answer with my current findings). – Vanity Slug - codidact.com Oct 03 '18 at 17:17