CODEWITHSUNDEEP
sqlodbcpyodbcsage-erp
Hassan Syyid
Hassan Syyid

Reputation: 1581

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!

Upvotes: 0

Views: 62

Answers (2)

Hassan Syyid
Hassan Syyid

Reputation: 1581

Turns out I switched to use the exact same DSN, SOTAMAS90 and ran my Python script as an admin and everything worked. I guess when running in my user context I wasn't able to access this view for whatever reason

Upvotes: 0

neutronHacker
neutronHacker

Reputation: 66

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.

Upvotes: 0

Related Questions