Sql-server – Long time, sporadic ‘String or Binary data would be truncated.’ Wisdom would be appreciated

error handlingsql serversql-server-2005

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.

Best Answer

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?

Related Solutions

Sql-server – How to identify the column(s) responsible for “String or binary data would be truncated.”

~~No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.~~

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.

Sql-server – The statement has been terminated

Insert into Employee_History Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Make sure the column order of the Employee_History table matches the order of the variables listed. The best practice is to list column names on INSERT statements.

The other possible cause is that the EmployeeName column in the Employee_History is shorter than 100 characters.

Importantly, as suggested in the comments, triggers fire once per statement instead of once per row so must be coded to handle multi-row inserts. The history table insert should be refactored as INSERT INTO Employee_History(column-list) SELECT column-list FROM inserted Not related to this error, though.

Also, there are a number of other errors in the trigger. An UPDATE statement can update multiple columns yet the trigger code identifies only one the potentially changed columns. The UPDATED function in an UPDATE trigger does not indicate the value has changed, only that it specified in the SET clause of the UPDATE statement. This may cause erroneous messages in the history table.

Best Answer

Related Solutions

Sql-server – How to identify the column(s) responsible for “String or binary data would be truncated.”

Sql-server – The statement has been terminated

Related Question