Search code examples
sqlodbcpyodbcsage-erp

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: screenshot of DSN configuration

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!

Solution

  • Some things to check:

    • Mismatches between 32-bit and 64-bit ODBC driver: Excel may be using the 32-bit ODBC driver you are showing, while your Python environment could be trying to use a 64-bit driver?
    • Python might be running from a different working directory, and not finding the file
    • User permissions from Python

    Edit: If other tables can be queried but not this specific view, check additionally for:

    • Are both ODBC connection strings/DSNs using the same credentials - perhaps certain views are restricted.