[SQL] Query a custom view from Sage 100 ODBC

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!

Continue reading...