Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
A client has an issue with failing reloads, they are getting:
OLEDB read failed
SELECT
opheadm.date_entered,
opheadm.date_required,
opheadm.customer location,
opheadm.class invoice_cat,
opheadm.pick_list pick_list,
opheadm.order_no order_no,
opheadm.invoice_customer customer,
opheadm.status status,
opheadm.customer_order_no cust_order_no,
CASE WHEN customer.analysis_code_6=' ' THEN 'MISSING' ELSE customer.analysis_code_6 END customer_group,
opdetm.warehouse+'#'+opdetm.product wh_product,
(CASE WHEN stockm.packaging='' or stockm.packaging=0 then 0 ELSE (CASE WHEN left(opheadm.order_no,2)<>'CN' AND opheadm.status='7' THEN opdetm.despatched_qty/stockm.packaging ELSE (SELECT CASE WHEN left(opheadm.order_no,2)<>'CN' AND opheadm.status<>'7' THEN opdetm.order_qty/stockm.packaging ELSE (SELECT CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty/stockm.packaging ELSE -opdetm.order_qty/stockm.packaging END) END) END) END) cases,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.order_qty ELSE (CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty ELSE -opdetm.order_qty END) END) quantity,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.order_qty*opdetm.net_price ELSE (CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty*opdetm.net_price ELSE -opdetm.order_qty*net_price END) END)/(CASE WHEN substring(opdetm.spare,16,1)=' ' THEN 1 ELSE CAST(substring(opdetm.spare,16,12) AS float) END) sales,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.order_qty*opdetm.net_price ELSE (CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty*opdetm.net_price ELSE -opdetm.order_qty*net_price END) END) sales#c,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.vat_amount ELSE (CASE WHEN opheadm.status='7' THEN -vat_amount ELSE -vat_amount END) END) vat#c,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.order_qty*(opdetm.net_price-opdetm.cost_of_sale) ELSE (CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty*(opdetm.net_price-opdetm.cost_of_sale) ELSE -opdetm.order_qty*(opdetm.net_price-opdetm.cost_of_sale) END) END)/(CASE WHEN substring(opdetm.spare,16,1)=' ' THEN 1 ELSE CAST(substring(opdetm.spare,16,12) AS float) END) gp,
(CASE WHEN left(opheadm.order_no,2)<>'CN' THEN opdetm.order_qty*(opdetm.net_price-opdetm.cost_of_sale) ELSE (CASE WHEN opheadm.status='7' THEN -opdetm.despatched_qty*(opdetm.net_price-opdetm.cost_of_sale) ELSE -opdetm.order_qty*(opdetm.net_price-opdetm.cost_of_sale) END) END) gp#c
FROM
live.scheme.opheadm opheadm WITH (nolock)
INNER JOIN live.scheme.opdetm opdetm WITH (nolock) ON opheadm.order_no=opdetm.order_no
INNER JOIN live.scheme.slcustm customer WITH (nolock) ON opheadm.invoice_customer=customer.customer
LEFT OUTER JOIN live.scheme.stockm stockm WITH (nolock) ON opdetm.warehouse=stockm.warehouse AND opdetm.product=stockm.product
WHERE
opheadm.status BETWEEN '1' AND '6'
AND opdetm.line_type IN ('P','S')
Any Ideas?
Andy
Turned out to be a source data quality issue!
A Product had been setup with a pack size of 2,000 in Sage – the “2,000” has a comma and has caused the SQL scripts to fail.
Once corrected it worked!!!
Thanks for the pointers
Often when I receive this error it's becuase I have multiple columns with the same name. For some reason if you have duplicate column names you will receive the ODBC fail read error message.
The reloads are failing in general or just sometimes?
Maybe a different oledb/odbc-driver could help. Further the approach of reducing the number of fields and/or different where-clauses could be useful to find the cause of the issue.
- Marcus
Turned out to be a source data quality issue!
A Product had been setup with a pack size of 2,000 in Sage – the “2,000” has a comma and has caused the SQL scripts to fail.
Once corrected it worked!!!
Thanks for the pointers