I am attempting to sync a custom view from Sage 100 using the ODBC connection. My DSN is correctly configured and I can query any standard tables with no issues:
However, when I run the following query:
SELECT * FROM vSalesHistory
I get the following exception:
pyodbc.Error: ('S0000', '[S0000] [ProvideX][ODBC Driver][PVKIO]Unable to locate and open the requested data file (101) (SQLExecDirectW)')
In Excel though with the following connection string:
DSN=SOTAMAS90; Description= MAS 90 4.0 ODBC Driver; Directory=|appserver02\ACCT\Sage2020MAS90; Prefix=|appserver02 ACCT Sage2020\MAS9015%,lappserver02\ACCT\Sage2020\MAS90\==\:ViewDLL=Nappserver02\ACCT\Sage2020\MAS90\HOME;LogFile=IPVXODBC.LOG;CacheSize =4;DirtyReads= 1;BurstMode=1;StripTrailingSpaces= 1;SERVER-NotTheServer
and this query:
SELECT vSalesHistory.CustomerNo, vSalesHistory.ItemCode, vSalesHistory.ItemType, vSalesHistory.ProductLine, vSalesHistory.ProductLineDesc, vSalesHistory.WarehouseCode, vSalesHistory.InvoiceDate, vSalesHistory.DollarsSold, vSalesHistory.CostOfGoodsSold, vSalesHistory.QuantityShipped, vSalesHistory.QuantityReturned, vSalesHistory.CustomerName, vSalesHistory.SortField, vSalesHistory.SalespersonNo, vSalesHistory.CustomerType, vSalesHistory.CustomerDiscountRate, vSalesHistory.ServiceChargeRate, vSalesHistory.CreditLimit, vSalesHistory.CustomerStatus, vSalesHistory.ItemCodeDesc, vSalesHistory.ExtendedDescriptionKey, vSalesHistory.ExtendedDescriptionText, vSalesHistory.PriceCode, vSalesHistory.AllocateLandedCost, vSalesHistory.ConfirmCostIncrInRcptOfGoods, vSalesHistory.ItemProductLine, vSalesHistory.CIItemProductLineDesc, vSalesHistory.ProductType, vSalesHistory.PrimaryVendorNo, vSalesHistory.VendorName, vSalesHistory.Category1, vSalesHistory.Category2, vSalesHistory.Category3, vSalesHistory.Category4, vSalesHistory.SalesPromotionCode, vSalesHistory.SaleStartingDate, vSalesHistory.SaleEndingDate, vSalesHistory.PlannerCode, vSalesHistory.BuyerCode, vSalesHistory.VendorItemCode, vSalesHistory.StandardUnitCost, vSalesHistory.StandardUnitPrice, vSalesHistory.LastTotalUnitCost, vSalesHistory.AverageUnitCost, vSalesHistory.SalesPromotionPrice, vSalesHistory.SalesPromotionDiscountPercent, vSalesHistory.TotalQuantityOnHand, vSalesHistory.LastAllocatedUnitCost, vSalesHistory.Volume
FROM vSalesHistory vSalesHistory
it works fine.
I am not sure where I'm going wrong. Any ideas? Thanks in advance!
Some things to check:
Edit: If other tables can be queried but not this specific view, check additionally for: