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 on 2008, works on 2000?? Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 2:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:23 AM
Points: 141, Visits: 522
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
Post #1351477
Posted Wednesday, August 29, 2012 3:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 7,230, Visits: 13,710
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
Post #1351492
Posted Wednesday, August 29, 2012 3:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
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
Post #1351494
Posted Wednesday, August 29, 2012 4:03 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:23 AM
Points: 141, Visits: 522
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
Post #1351497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse