OLE DB/ODBC Error: current operation was cancelled because another operation in the transaction failed

[caballaire]

Hi-

I cannot get an SQL query to import from SAP Business One. When I run the query in SAP B1, the query works perfectly. I am simply syncing to an SQL DB from PowerPivot and importing via the query below. I am not too terribly versed in this as I used a query generator in SAP. What am I missing?? Is there a records limitation?????



SELECT T0.[CardCode] as 'Cust. No.', T0.[CardName] as 'Cust. Name', T0.[DocNum] as Document, T0.[DocCur] as Currency, T1.Rate, T0.[DocDate], T1.TotalFrgn, T1.[SubCatNum] as 'BP Catalog',T1.[ItemCode], T1.[Dscription] as 'Item Descr.', T1.[Quantity], T1.[Price] as 'Sale price', T1.[LineTotal] as Revenues, T1.VatSumSy as 'Revenues VAT', T0.TotalExpns as Freight, T8.[Segment_0], T8.[AcctName], T3.U_ProductGroups, T3.U_ProductCategories, T5.[GroupName],T2.U_Dtmdiv,T6.Name as Country,
T7.[ItmsGrpNam],T4.SlpNAme, T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS], T0.Comments, T0.NumAtCard


INTO #T_TEMP


FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode
LEFT OUTER JOIN OCRY T6 ON T2.Country = T6.Code INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod INNER JOIN OACT T8 ON T1.AcctCode = T8.AcctCode




UNION ALL


SELECT T0.[CardCode] as 'Cust. No.', T0.[CardName] as 'Cust. Name', T0.[DocNum] as Document, T0.[DocCur] as Currency, T1.Rate, T0.[DocDate], T1.TotalFrgn, T1.[SubCatNum] as 'BP Catalog',T1.[ItemCode], T1.[Dscription] as 'Item Descr.', -T1.[Quantity], T1.[Price] as 'Sale price', -T1.[LineTotal] as Revenues, -T1.VatSumSy as 'Revenues VAT' , -T0.TotalExpns as Freight, T8.[Segment_0], T8.[AcctName], T3.U_ProductGroups, T3.U_ProductCategories, T5.[GroupName],T2.U_Dtmdiv,T6.Name as Country,
T7.[ItmsGrpNam],T4.SlpNAme, T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS], T0.Comments, T0.NumAtCard


FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode
LEFT OUTER JOIN OCRY T6 ON T2.Country = T6.Code INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod INNER JOIN OACT T8 ON T1.AcctCode = T8.AcctCode




where T0.CANCELED = 'N' and T0.ObjType = 14


SELECT [Cust. No.], [Document], T0.[Currency], (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN Null ELSE T0.Rate END) as 'Rate', [DocDate], (CASE WHEN T0.TotalFrgn = 0 THEN Null ELSE T0.TotalFrgn END) as 'Total FC', [BP Catalog], T0.[ItemCode], [Item Descr.], [Quantity], (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN T1.Price ELSE T1.Price * T0.Rate END) * T0.Quantity as 'Purchase Price', (CASE WHEN ISNULL(T0.Rate,0)=0 OR T0.Rate = 0 THEN T1.Price ELSE T1.Price * T0.Rate END) as 'Costs', [Revenues], [Revenues VAT], [Freight], (CASE WHEN ISNULL(T0.[Freight],0)=0 OR T0.[Freight] = 0 THEN Null ELSE T0.[Freight]/10 END) as 'Freight GST', [Sale price], [Segment_0], [AcctName], T0.U_ProductGroups, T0.U_ProductCategories, [Country], [U_Dtmdiv] as 'Business Line', [Cust. Name], [SlpNAme] as 'KAM', [GroupName], [ItmsGrpNam], T0.[U_ORDERED], T0.[U_TMPREFERENCE], T0.[U_CLASS], T0.[U_JOBS], T0.Comments, T0.NumAtCard as 'Customer Ref.No.'


FROM #T_TEMP T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN OPLN T2 ON T1.PriceList = T2.ListNum LEFT JOIN ORTT T3 ON T0.DocDate = T3.RateDate AND T1.Currency = T3.Currency


WHERE T2.ListName = 'PURCHASE'


ORDER BY [DocDate],[Document]