Sage ODBC with Sage 100 V2020

Question

I am upgrading a client to Sage 100 V2020 and am running into issues with some Excel spreadsheets that have been working for years in prior versions of Sage 100. In V2020 the client upgraded their Excel version from 2013 to Excel 365 Enterprise 64 bit installed locally on the server. I installed the 64 bit Sage 100 ODBD driver and I can connect to the Sage 100 database from inside Excel using Microsoft Query and download the data to Excel. However, if I try to sort the query by Customer No, I get a Microsoft Error when I try to refresh the query:

I have admin rights on the server that I'm running the queries on. Every old Spreadsheet query that involves sorting by the Customer fails. If I remove the sorting, it refreshes fine. I'm wondering if Providex has an update ODBC driver that we need to install for Excel 365 Enterprise. I'm using Providex ODBC driver 64 bit v4.40.0004 currently. Any thoughts would be appreciated! Thanks!

Alnoor_C · Answer

Kevin - I seem to recall the Path to Views DLL has a 32-bit component. Maybe it's interfering. Maybe you are inclined to test that :-) If you do, I would setup a new Linked Server separate from your current MAS_ABC64. Now if you do this by passing the System DSN name, then prior to this setup the silent connect DSN so Path to Views DLL is blank. But if you set it up by passing the connection string instead, then omit the ViewDLL= part.

jcnichols · Answer

Hi Mark, I'm still researching but I'm not convinced that the issue is with the Sage 100 ODBC x64 driver. I can run the above SQL statement in other x64 applications without any issue (i.e. 2020 Crystal Reports, ODBC query tools), the SQL statement runs using MS Query in in Office 365 x64 Excel without issue. The SQL statement fails as you described if I use the MS Query wizard. 
 I'll follow up to this thread when I'm able to provide additional information. 
 Thanks John

Another1 · Answer

I know this is an old post but I can tell you everything I had to go through to correct this. Hopefully it can help some. 
 One of the biggest problems I had was when our IT Net Admins makes changes to group policies or does not give the correct permissions in the first place. Per previous tickets with Sage support, users must have full permissions to their Sage folders and BI Files. In this example it was just being able to run he SIR add-on in Excel. Not only was I not able to install it properly, but missing action options in Sage client software as well. Blocking clients from have full access to the MAS 90 driver caused problems because they could not find the driver that they had created prior to the change and now they required admin privileges to set it up. If Admin ran ODBC as Admin and setup a System DSN, the user still couldn't use it. That was actually a couple years nightmare for me. 
 The majority of my findings with this particular error message, was the server odbc service would suddenly stop because it was overloaded. Tickets would flood in because they couldn't complete tasks in Sage. I found that they did not build the server to the required specs and it did not have enough resources to process all the user accounts that had tasks set to spawn from the server. All this killing resources. I was constantly having to log onto the server to start the task again. We had a script scheduled to run if the service stopped, but only up to three times so we could investigate. But the service fails for a reason and if an ODBC task is still running somewhere, it fails pretty quickly back-to-back. 
 I would love for someone to tell me how to create a log to target what user and what table(s) are causing a server-side odbc service failure. Because there have been times it was a locally save custom Crystal Report and an Access Db with just about every table available connected all refreshing at the same time. The only way I figured it out was because they were connected via TS. 
 Other items to check: 
 
 user role has correct permissions on top of their user account having ODBC enabled. One missing check in the roles assigned can cause this 
 Decide if task is spawned locally or server-side. Check requirements for either are correct and meet the needs of any query. The bigger the query the more demand. 
 If you have 64-bit version of Sage Server, they recommend installing 32-bit first when upgrading from a 32-bit, then installing the 64 bit. I can only assume the same would apply to client-side. At some point in time I was began to have issue, I had only 32 installed then added 64 and it fixed it. I find Sage install processes are finicky in the order it's complete too. Older --> Newer 
 
 Today, 2024, the issue has returned. Back to investigating and seeing what was changed without notice......again. Good luck and if anyone finds the full outcome, PLEASE share

Sage 100

Sage ODBC with Sage 100 V2020

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING