March 27, 2025 at 7:15 pm
Forgot I posted a similar issue back in 2020, String or Binary Truncation Error Strange Behavior, but here we are again, 5 years later with a somewhat similar issue. Here is the message from SQL Server:
String or binary data would be truncated in table 'dbo.BAT_Test', column 'NewCode'. Truncated value: ''.
Basically, a stored procedure runs daily to process data (CSV files 255 fields wide) into a SQL table, BAT_All, then normalize the data into various tables.
Here is what we know:
• In the main import table, BAT_All, the field being used to insert into a normalized table BAT_Test is a varchar(5). The "NewCode" field in BAT_Test is a char(3) (hey... this is 15 years old and I am not the original architect).
• Looking at the query that is inserting to the BAT_Test, none of the values exceeds 3 characters. Used both LEN() and DATALENGTH() to test the values. We also looked for any hidden (non-ascii) characters, none found in all of the raw data.
• The entire dataset, before the WHERE clause/filters does contain records that are NULL and records that are 4 characters wide, but none of these would be inserted due to the WHERE clause/filters.
• If we apply a LEFT(case statement end ,3), there is no truncation error.
• Similarly, if we modify BAT_Test and change "NewCode" to a varchar(4), there is no truncation error, but again, there are no values that exceed 3 characters.
• The SQL Server version is 15.0.4385.2 (SQL Server 2019, CU 28).
We had a similar issue about a month ago when converting a string date value into a smalldatetime. The value causing the truncation error was outside of the smalldatetime boundaries but should never have been encountered due to the WHERE clause/filters. Note that in this case and the smalldatetime case, the "truncation value" is returning as a blank.
It seems to me that the data is being evaluated before the filters are applied?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 27, 2025 at 9:13 pm
Create a temporary table to load into, with len of 5+, and see if any rows with data longer than 3 bytes are actually getting loaded.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2025 at 2:11 pm
Thanks for the suggestion!
Looking at the incoming data, the biggest field coming in was 4 characters, so we did change a replica table to be varchar(4). While that removed the truncation message, none of the data in the BAT_Test table exceeded 3 characters. We did try it as a varchar(5) and still received the same results (nothing exceeded 3 characters).
At this point, I think the SQL engine itself is doing something prior to the filter, i.e., preparing the "inserts" before the filters, thus causing the truncation error. Just seems odd that the truncated value is listed as a blank.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 28, 2025 at 2:46 pm
Can you provide the relevant table structure and an insert statement that fails?
😎
Keep in mind that the string manipulation functions, such as LEFT,RIGHT and SUBSTRING do imply trimming of non-printable characters!
March 28, 2025 at 4:16 pm
Can you provide the relevant table structure and an insert statement that fails?
LOL... love seeing people ask for that. Unfortunately, this is proprietary so we cannot. I was hoping someone with more in-depth SQL internals might have some insight?
But there is just a CASE statement, no LEFT, TRIM or any other function to manipulate the insert. We just know that, based on the filters, we should not (I tell my kids ALWAY/NEVER/CANNOT are words that should not exist, LOL) receive any codes that exceed 3 characters. We did start a data review to just make sure that the "assumption" was still correct (all codes are 3-characters with this filter).
I updated the original post... when we were looking for non-printable characters, we looked at the entire raw data set, not just the filtered data. We also reviewed min/max values from the SELECT statement, MIN/MAX data lengths, etc., checked to ensure a field was not "shifted" (CSV and mis-placed quotes). Everything from the SELECT statement with the filters appears to be correct.
Just as a fix, we did have to add a LEFT(case statement,3) so we can continue processing.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 28, 2025 at 4:28 pm
LOL... love seeing people ask for that. Unfortunately, this is proprietary, so we cannot. I was hoping someone with more in-depth SQL internals might have some insight?
He he he, if you cannot help us helping you, then you have a problem!
😎
Table structures and single insert statements are not sensitive information, data can easily be obfuscated to prove the repeatability of a failure. without such information, you are leaving everyone guessing.........
When it comes to the internals of SQL Server, most of us on this forum have forgotten more than most people will ever learn.
March 28, 2025 at 4:40 pm
Can you provide the relevant table structure and an insert statement that fails?
LOL... love seeing people ask for that.
You think it's funny that people are trying to help you by asking for repeatable and tangible evidence of your issue? Bizarre.
March 28, 2025 at 4:46 pm
Sorry, I appreciate everyone's help.
Is there a way to delete this post?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 31, 2025 at 2:21 pm
As an FYI, columns can be renamed. I work in a place with propriatary information and do it all the time. I make my data samples equal references to Disney World or Doctor Who, etc. I scrub out server/instance names. It can be done and everyone here does it.
It is a little bit extra work, but with that work, we can help better. The problem is your original post removes most of the things we could suggest as the problem, so having this extra information really is necessary for us to be able to dig deeper.
But, as a random guess, your CSV file(s) that fail actually are showing the source field width as wider than 3 characters, even if the data inside the file isn't. You would need to check the source definitions and the column delimiter properties to verify this.
March 31, 2025 at 5:02 pm
Not sure if this is relevant, but UTF-8 support was introduced with MS SQL 2019. Is this something you use in either of those two tables?
April 2, 2025 at 10:04 am
This looks similar to a problem I posted a few years back (link below). It appears that there are some situations where data conversion is attempted before the filters are applied. There are links in the thread that may help explain what's going on.
https://www.sqlservercentral.com/forums/topic/weird-cast-try_cast-behaviour
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply