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..
--distinct (st.old_store_reference), LEN(st.old_store_reference)
FROM SAP_CT_Codes s
,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'