In my position in support, I see plenty of cases where you try to run a report or insert data somewhere in the application from an interface and it throws a string or binary data will be truncated error. I know this means it is trying to insert a value that exceeds the max length of the column in a table. Does anyone know an easy way to find all records in the database that have values exceeding the column length in a table without having to search a needle in a haystack? I know how to find in one table/column but surely there has got to be a quick way to find the culprit?
Which specific version of SQL Server are you on? SQL 2012? SQL 2016? ?
They can't, that's why you're receiving that error, someone is trying to INSERT or UPDATE the column to a value that's too long. I can't see how it would happen when running a report, unless the report runs a stored procedure that INSERTs or UPDATEs.
It's really a user interface problem, however the user is accessing the database, there needs to be some limit on the length of the input control. If they are writing a SQL script, or using something that generates SQL, it could perhaps check the length of the values against the table schema before it actually tries it.
You can modify the server settings to get a more descriptive error message, but it still throws the error:
1 Like
I support clients with various versions of SQL. Not sure if the version matters for my question though? Just looking for a query or easy way to find records in a database that have data exceeding any max column length.
Our reports do indeed insert data. They generate temp tables that insert data to create the report. However, this has nothing to do with reports. The error happens when the system attempts to import external data into our application (practice management) and happens either when running the report that pulls this data, or when opening charge entry where billable charges are entered. Something being imported is exceeding a column length. I suspect something in the ext_charge_holding_tank table which is where the charges are stored before they get imported into our app. Usually if this comes up it's almost always going to be data being imported from an external/3rd party via an interface such as Rosetta. I'm just trying to figure out an easy way or at least less tedious way to find what it is trying to insert into what table and what column that exceeds the max length. Right now all I know to do is query a specific table and column where length (column_name) > whatever the max is defined in the table. That can become a bit of a needle in a haystack. Tried running a profiler trace but again, needle/haystack and likely not going to get me what I need. I do wonder if there is a parameter in profiler that looks would tell me when it queries something of this nature?
SQL Server is not going to allow data to go into a column that exceeds the max column length. I can't imagine that any other dbms would either. You can identity attempted INSERTs for new data, but not for old data.
1 Like
OK, is there a suggested way to locate perhaps via a profiler trace parameter which column the system is trying to write to that exceeds the max length? I usually run traces to troubleshoot performance issues but might a trace have the ability to tell me what is triggering this error? Or some other tool perhaps?
It does matter, because there's a SQL Server flag setting (if it's SQL Server?!) that can be used in later versions to make SQL return details on the error when an INSERT of a too-long value is attempted.
I wouldn't have asked if it didn't matter :-).
OH gotcha. So I guess if you can tell me about this feature/setting and what version it starts on, I can check it out. My version is 2022 and 2019 on my machine. Most clients will be on 2016 and newer.
If you are on 2022 and 2019, I think you should already be seeing those messages. On 2016, you have to set a SQL flag to get the specific message.
There are also some things to watch out for but still good for debugging.
How to fix the error "String or binary data would be truncated" - Brent Ozar Unlimited®
1 Like
sounds great in theory but I tried enabling verbos warning messages per the link in 2019 but it did not work. still get the same error syntax. Anyone have experience with this?
continue here
Hi
Hope this helps
DECLARE @TableName NVARCHAR(256) = 'YourTableName';
DECLARE @ColumnName NVARCHAR(256) = 'YourColumnName';
DECLARE @MaxLength INT;
SELECT @MaxLength = CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName;
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM ' + @TableName + ' WHERE LEN(' + @ColumnName + ') > ' + CAST(@MaxLength AS NVARCHAR);
EXEC sp_executesql @SQL;