OLEDB Read Failed

[awhitfield]

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

[Anonymous]

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.

[marcus_sommer]

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

[awhitfield · Accepted solution]

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