SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String or binary data would be truncated on 2008, works on 2000??


String or binary data would be truncated on 2008, works on 2000??

Author
Message
simon.letts
simon.letts
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 606
Hi, I'm in the process of migrating a system from SQL2000 to SQL2008 R2 (both Ent Edition 64 bit). I'm testing all the batch jobs and one is failing with 'String or binary data would be truncated'. I've fixed many of these before where the column width of the table you are inserting to is not wide enough, however this appears different. I've listed a cut down version of the code below. But things to note..

a) The SAP_SITE Column on the SAP_CT_DETAIL table being inserted to is a varchar(4) - If I change it to wider the job will work!!?? However....

b) If I run the select using the distinct option (commented out below) it comes back with 40 rows all of LEN = 4

c) If I run it with the insert it fails with the String or binary data would be truncated message.

d) I know the tables being joined do have data with over 4 chars in them but these are not being picked up by the join and are excluded from the select result.

e) This is quite old code which I've re-written with new style join efficiency and it fails with exactly the same thing.

f) I could just increase the column width to get it through but would like to understand what it's trying to do.

Thanks in anticipation..

INSERT INTO
SAP_CT_detail (
[sap_site]
)
SELECT
st.old_store_reference
--distinct (st.old_store_reference), LEN(st.old_store_reference)
--,len (st.old_store_reference)
FROM SAP_CT_Codes s
,stocktake so
,stocktakecount stk
,stocktakecountline stkl
,store st WITH (nolock)
,itemcoloursize ics WITH (nolock)
,itemcolour ic WITH (nolock)
WHERE s.store_code = st.store_code
AND s.stocktake_code = so.stocktake_code
AND so.stocktake_code = stk.stocktake_code
AND s.stocktake_code = stk.stocktake_code
AND stk.stocktakecount_code = stkl.stocktakecount_code
AND stkl.itemcoloursize_id = ics.itemcoloursize_id
AND ics.itemcolour_id = ic.itemcolour_id
AND stk.docstatus_ind = 'C'

Thanks again
ChrisM@Work
ChrisM@Work
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: 16582 Visits: 19557
The easiest way I could replicate this behaviour was by using the ANSI_DEFAULTS setting:

SET ANSI_DEFAULTS Off -- change to ON to raise error

DROP TABLE #SAP_CT_Codes
CREATE TABLE #SAP_CT_Codes (old_store_reference VARCHAR(8))
INSERT INTO #SAP_CT_Codes (old_store_reference) VALUES ('FOUR '),('FOURx ')

SELECT old_store_reference, LEN(old_store_reference), DATALENGTH(old_store_reference)
FROM #SAP_CT_Codes


DROP TABLE #SAP_CT_detail
CREATE TABLE #SAP_CT_detail ([sap_site] VARCHAR(4))
INSERT INTO #SAP_CT_detail ([sap_site])
SELECT old_store_reference
FROM #SAP_CT_Codes

SELECT sap_site, LEN(sap_site), DATALENGTH(sap_site)
FROM #SAP_CT_detail



I think it's more likely to be a D'Oh moment though.
Firstly, consider using ANSI-92 joins instead of ANSI-89 style joins. They are easier to visually scan and verify - and the outer join syntax of ANSI-89 has already been deprecated and is unavailable in SQL Server 2012.
With a new predicate in the WHERE clause, the select part of your query looks like this...

SELECT
st.old_store_reference --,
--distinct (st.old_store_reference), LEN(st.old_store_reference)
--,len (st.old_store_reference)
FROM SAP_CT_Codes s
INNER JOIN stocktake so
ON s.stocktake_code = so.stocktake_code
INNER JOIN stocktakecount stk
ON so.stocktake_code = stk.stocktake_code
AND s.stocktake_code = stk.stocktake_code
AND stk.docstatus_ind = 'C'
INNER JOIN stocktakecountline stkl
ON stk.stocktakecount_code = stkl.stocktakecount_code
INNER JOIN store st WITH (nolock)
ON s.store_code = st.store_code
INNER JOIN itemcoloursize ics WITH (nolock)
ON stkl.itemcoloursize_id = ics.itemcoloursize_id
INNER JOIN itemcolour ic WITH (nolock)
ON ics.itemcolour_id = ic.itemcolour_id
WHERE LEN(st.old_store_reference) > 4



Give it a shot.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 860
i know since sql 2000 there have been some changes to the way queries are evaluated... you get the same type of errors is you try and insert from a column containing character data into an Int field, even if you have excluded all of the char data and there is only int data stored in a char field.

SQL isn't going to evaluate every peice of data in your source column, so if it's a char(5) then there is a potential for a data clash

try casting the selected column as char(4)

MVDBA
simon.letts
simon.letts
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 606
Hi, thanks for your help on this.

Your code didnt quite work as expected (select returned 0 rows), but as mentioned I'd already tried that to give exactly the same error. (Thanks for the heads up re this being depracted in 2012 tho!).

I did find that setting ANSI WARNINGS OFF allows it to work...although not a good thing to do. I've also tried running it by selecting LEFT(tablename,4) and the insert works fine usnig this, so will go with that option for now.

I just can't find anything telling me why it's doing it though, so hope I don't get any further issues with other code being run !?!?

Thanks again
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