String or binary data would be truncated. Error when inserting empty table results into TEMP table

  • sqlsurfing

    SSCarpal Tunnel

    Points: 4883

    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 😀

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • sqlsurfing

    SSCarpal Tunnel

    Points: 4883

    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 😀

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • sqlsurfing

    SSCarpal Tunnel

    Points: 4883

    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 😀

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • sqlsurfing

    SSCarpal Tunnel

    Points: 4883

    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 😀

  • steve.bradford

    SSC Journeyman

    Points: 93

    Are any records returned if you use DATALENGTH(ID) > 15? LEN() automatically trims.

  • drew.allen

    SSC Guru

    Points: 76662

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply