August 29, 2012 at 2:52 am
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)
FROMSAP_CT_Codes s
,stocktake so
,stocktakecount stk
,stocktakecountline stkl
,store st WITH (nolock)
,itemcoloursize ics WITH (nolock)
,itemcolour ic WITH (nolock)
WHEREs.store_code = st.store_code
ANDs.stocktake_code = so.stocktake_code
ANDso.stocktake_code = stk.stocktake_code
ANDs.stocktake_code = stk.stocktake_code
ANDstk.stocktakecount_code = stkl.stocktakecount_code
ANDstkl.itemcoloursize_id = ics.itemcoloursize_id
ANDics.itemcolour_id = ic.itemcolour_id
ANDstk.docstatus_ind = 'C'
Thanks again
August 29, 2012 at 3:51 am
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.
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
August 29, 2012 at 3:55 am
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
August 29, 2012 at 4:03 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply