Does the error occur on other tables using the x64 driver?

C:\Windows\System32\odbcad32.exe

Select the SOTMAS90 driver from the User DSN Tab

Go to the DEBUG tab and test your connection.

If the connection is successful, go to the Options Tab and select Null Date, click apply and rerun your query.

If the query is successful, their are bad dates in the file.

John Nichols

Sage 

The connection tested successful and I checked to "on" the NULL Date setting on the options tab and retested the connection as successful, but it failed with the same error when I refreshed the data. I also made the same change (i.e. EnforceNULLDate=1; ) in the properties of the query's connection. EnforceNULLDate was not specified, so I added it, saved the change, closed Excel and re-opened/tested but it failed again with the same error. I have also tried with and without enabling the C/S ODBC Driver. I also tried running the CheckDate utility, but it reported nothing. It's really a shame to have the client re-install the 32-Bit version of Office 365.

Perhaps try removing columns from your query, one by one, to narrow down the issue?

Good idea. I'll try, but the oddest thing is that the same query (there are several queries that fail - using multiple workstations and multiple user ids - all against large history tables) that fails will occasionally work. It has yet to fail with 32-Bit Excel.

Hi.

"will occasionally work"  .  Can you post your connection string (Obscure the server name) and the SQL statement?  The next time it fails paste the connection string "Directory" value into the workstation's file explorer path.

As Kevin suggested restrict the SQL statement to fewer columns or just the key value of the table.

It's either the connection string or the SQL statement. There is no need to move them back to x86 Office.   

Failing Connection String:

DSN=SOTAMAS90;Description=MAS 90 4.0 ODBC Driver;Directory=..\MAS90;Prefix=..\MAS90\SY\, ..\MAS90\==\;ViewDLL=..\MAS90\HOME;LogFile=\PVXODBC.LOG;RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer

1. If I removed the C/S ODBC parameters:
   RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222;
   from the connection string it works with 64-Bit.
2. Eliminating fields in order to isolate a problem field value proved inconclusive
   1. If I eliminated fields from the query down to just 'ReferenceDate' it sill failed
   2. If I replaced 'ReferenceDate' with  'TransactionDate' it worked - however...
   3. If I put 'ReferenceDate' back into the SQL statement it still worked.
   4. If I used 'TransactionDate' instead of 'ReferenceDate' in the original query (before eliminating14 other fields) and using the C/S ODBC parameters, the query still failed.
   5. Thus I concluded that 'ReferenceDate' was not the issue. I had also written a Crystal Report and a MS Access (32-Bit) query that grouped all the ReferenceDate field values in the table and no unusual dates were found.
3. What I learned:
   1. 32-Bit Excel works with the Sage 100 ODBC (32/64Bit) driver with C/S ODBC enabled or disabled against large history tables on version 2019 (6.10.5.0)
   2. 64-Bit Excel with the Sage 100 ODBC (32/64-Bit) driver has an 'Internal Error' problem with C/S ODBC enabled in the connection string when run against large history tables on version 2019 (versions tested: 6.10.0 & 6.10.5.0, both failed with the same error)
   3. My earlier observation that queries would "occasionally work" is explained by how connection string is used. Excel uses the connection string in the query instead of the current connection string in the ODBC control panel DSN (e.g. SOTAMAS90). Thus if a query is created while the C/S ODBC is enabled, the connection string in the Excel file will retain those settings even after the C/S ODBC is disabled for the current (or last) Sage 100 logged-in user in the Windows session. One might think that C/S ODBC is turned off after disabling it in User Maintenance because the current DSN doesn't show "Server" tab parameters although it remains active because it is triggered by the "Server" parameters in the connection string stored in the Excel file if the C/S ODBC Driver service is running.

Great detective work! Better than reading a mystery story.

Did you try restarting the CS ODBC service to see if that helps?

Hi, 

Never use the SOTAMAS90 DSN in a connection string.
The SOTAMAS90 DSN values are updated each time Sage 100 is started and are retained until another workstation is started.
If you have multiple versions of Sage 100, the values to the directory value will be different.

Create a NEW DSN or change your connection string to reference the Driver directly (See Below)

driver={MAS 90 4.0 ODBC Driver};Directory=..\MAS90;Prefix=..\MAS90\SY\, ..\MAS90\==\;ViewDLL=..\MAS90\HOME;LogFile=\PVXODBC.LOG;RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer

John Nichols

Sage

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. ;-)

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.