November 19, 2013 at 4:31 am
Has someone ran into something strange like this where no data is being inserted but this error happens.
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
In our production environment there is a Temp table created where "id" column is defined as varchar(15)
along with other columns defined. There is a large query 5 table join ...that "inserts" into the temp table.
There is a table part of the large query that has a column called table1.ID to simplify. Looking at the defined column. "table1.id" is defined as char(25)
** I didn't write the code, it's odd even how it was implemented. Anyhow recently developers mentioned an ssis job had been failiing..(only recently past 4 days... we checked the data and we've had records in the system last month with character size more than 15 so maybe not a data issue... could it be data corruption or maybe I'm just missing something) he found the fix, but it requires changing it to "varchar(25)" in the TEMP Table.. but I'm still stumped at the reason why it fails. Wanted to ask if someone could help explain.
The actual query is joining about 5 tables.
Psuedo code:
insert into #TEMPTTABLE
select columns
join table1
join table2
join table3
join table4
join table5
Where
Some Criteria
= SUCCESS (500,000 records inserted)
insert into #TEMPTTABLE
select columns
join table1
join table2
join table3
join table4
join table5
Where
Some Criteria
AND len(ID) > 15 -- insert records > 15 characters in length
= FAILED ERROR "String or binary data would be truncated."
Let's find the suspect records
select columns
join table1
join table2
join table3
join table4
join table5
Where
Some Criteria
AND len(ID) > 15 -- insert records > 15 characters in length
= RESULTS RETURNED ZERO ROWS... I thought that was odd
So in summary:
An insert into yielded with the multi-table join = works, inserts records
An insert into yielded with the multi-table join adding criteria records > 15 char = Fails error msg.
A "select" to research suspect records with id's > 15 ... using the mult-table join (omitting the Insert #TEMPTABLE) but adding criteria records > 15
= Returns NO records... but fails to insert (0 records - since the select yields zero records) when combined with #TEMPTABLE insert.... but displays error msg:
Any thoughts?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
November 19, 2013 at 4:43 am
It might be possible that there are rows with length > 15 selected early on in the query plan which are filtered out later.
If you change the query, the query plan might change as well and in some part of the plan the maximum length is violated.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 20, 2013 at 1:57 am
Thanks for pointing that out, I haven't experienced that before- didn't know that's possible. Ill have to practice/read more on query plans. Ill give that a try tommorow.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
November 20, 2013 at 2:07 am
sqlsurfing (11/20/2013)
Thanks for pointing that out, I haven't experienced that before- didn't know that's possible. Ill have to practice/read more on query plans. Ill give that a try tommorow.
I've bumped into similar issues a few times. Most of the time they are conversion issues from string to integer.
The final result set contains only integers, but some rows read from disk are not an integer. If the query optimizer decides to do the conversion early in the query plan, these rows might still be present and you get a conversion error.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 20, 2013 at 6:05 pm
Hi Koen, thanks for the insight into SQL, would you happen to have an example of this? (simple one or pseudo code) To be better understand?
Trying to relate this the issue I'm seeing. I did some rudimentary changes to order of tables and changed where clause. The resulting error remained.
Is there some technique to help avoid, or know where to start looking?
Thanks
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
November 21, 2013 at 12:33 am
Sorry, I don't have an example with me. I encountered the issue at another client.
It also doesn't happen very often. The only trick was to make sure rows that could not be converted were already filtered out.
You could do this for example with a CTE. Or you could add extra checks to see if a row could be converted to int.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 21, 2013 at 1:06 pm
Thanks Koen for the suggestions, I will experiment with this and try to come up with fix/sample for myself.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 27, 2016 at 3:16 pm
Are any records returned if you use DATALENGTH(ID) > 15? LEN() automatically trims.
October 27, 2016 at 4:19 pm
steve.bradford (10/27/2016)
Are any records returned if you use DATALENGTH(ID) > 15? LEN() automatically trims.
Since this thread is almost three years old and the OP hasn't made any recent updates, it's likely that this was resolved a long time ago.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply