Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String or binary data would be truncated. Error when inserting empty table results into TEMP table Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 4:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:12 AM
Points: 132, Visits: 934
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
Post #1515495
Posted Tuesday, November 19, 2013 4:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 13,528, Visits: 11,323
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515497
Posted Wednesday, November 20, 2013 1:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:12 AM
Points: 132, Visits: 934
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
Post #1515942
Posted Wednesday, November 20, 2013 2:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 13,528, Visits: 11,323
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515945
Posted Wednesday, November 20, 2013 6:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:12 AM
Points: 132, Visits: 934
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
Post #1516283
Posted Thursday, November 21, 2013 12:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 13,528, Visits: 11,323
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1516335
Posted Thursday, November 21, 2013 1:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:12 AM
Points: 132, Visits: 934
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
Post #1516568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse