Kevin M I did try restarting the C/S ODBC service, but that didn't prevent the 'Internal Error'.

jcnichols Yes, I have been seeing how using DSN=SOTAMAS90 causes Excel's stored connection string parameters to change and make for, seemingly, inconsistent results, but that's how Excel creates them when using MS Query or Power Query (unless Power Query Advanced mode is used).  I had to be careful to restart Excel after each test because once a connection was made the connection parameters persisted.

New Information with 'work-around' for 64-Bit Excel!

1. So far the error is only happening with 64-Bit Excel using the Client/Server ODBC driver against tables that have expanded to a 2nd file (i.e. mm_xxxxxxx.M4T.001)
2. The error can be avoided while retaining C/S ODBC performance by adding the UID, PWD, and Company parameters to the connection string. Also, using the "DRIVER" parameter instead of the "DSN=SOTAMAS90" parameters is recommended if one wants to avoid changes being incorporated into the connection string from future SOTAMAS90 DSN settings. Example:
   
   DRIVER={MAS 90 4.0 ODBC Driver};UID=user_login;PWD=password;Company=ABC;Directory=M:\Sage\Sage 100\MAS90;Prefix=M:\Sage\Sage 100\MAS90\SY\, M:\Sage\Sage 100\MAS90\==\;ViewDLL=M:\Sage\Sage 100\MAS90\Home\;LogFile=\PVXODBC.LOG;RemotePVKIOHost=YOUR_SAGE_SERVER;RemotePVKIOPort=20222;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer.
3. One should consider the security exposure of including credentials in the Excel connection string. I created a user with minimal access for this purpose
4. jcnicholsI hope this helps to get this corrected. Also, the client wants to know to whom they can send my bill. ;-)

It gets better!

Converting theses Client/Server ODBCqueries to Power Query improved performance by cutting run time almost in half. Further, converting the date range criteria statements from M language into ProvideX SQL as part of the source SQL, cut the run time in half again.

Also, Power Query did not encounter the "internal error" when using Client/Server ODBC as does MS Query which was the original issue described in the posting.

As a general rule, would changing the DSN to driver={MAS 90 4.0 ODBC Driver} improve performance on an old-fashioned MS query or is the performance uptick only applicable on Power Queries??

jcnichols  - It has been a couple years now and I still encounter this issue in v2023.  Should users hold on to ANY hope that this fairly arcane technical issue EVER be resolved?  I am sure the tech team is focused on more ordinary issues.  Just asking for a "friend".

After re-reading jcnichols post above and testing it, I see that one can manually replace "DSN=SOTAMAS90" with "driver={MAS 90 4.0 ODBC Driver}" to avoid the error by using parameters different than what Sage 100 resets them on login (i.e. cache=0 or others that work as well). It's still a work around though. I wish John had a bright green logo below his name as I wasn't familiar with him as with other Sage folks.

Still happening in 2024 and not with C/S ODBC, just regular old Standard SOTAMAS90. Changing cache=4 to cache=0 solved the problem.

To eliminate the unresolved issue, I had the client go back to 32 bit Office.  Works fine in that environment.

I have a connection using the DNS=SOTAMAS90 and consistently get the "[ProvideX][ODBC Driver] Internal Error."  When I try to set up a DRIVER={MAS 90 4.0 ODBC Driver} connection, it fails the logon.  It's not possible for me to convert back to a 32 bit version of excel.  Any help would be appreciated.

Hello,

Can you post your connection string (Obscure the server name/login/pwd) and the SQL statement?