Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sqlsurfing
sqlsurfing
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 1130
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 :-D
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlsurfing
sqlsurfing
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 1130
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 :-D
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlsurfing
sqlsurfing
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 1130
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 :-D
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlsurfing
sqlsurfing
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 1130
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 :-D
steve.bradford
steve.bradford
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 32
Are any records returned if you use DATALENGTH(ID) > 15? LEN() automatically trims.
drew.allen
drew.allen
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2611 Visits: 9878
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search