"[ProvideX][ODBC Driver] Internal Error"

Question

This is happening with a single table MS Query in Excel 64-Bit when accessing large individual I/M and P/R files and continues after rebuilding them (Ex. IM_ItemTransactionHistory) . I reviewed the other thread on this error, but nothing applies other than the exact message and Sage 100 level 2019 (version 5 in this case). It does not happen in 32-Bit Excel and I have properly installed and re-installed the 32/64 Bit ODBC driver. I also get a similar error when using 64-Bit Power query. I'm thinking this has to be something installation dependent, but they have no 3rd party add-ons and is a straight distribution setup.

connex · Accepted Answer

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.

connex · Answer

New Information with 'work-around' for 64-Bit Excel! 
 
 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) 
 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. 
 One should consider the security exposure of including credentials in the Excel connection string. I created a user with minimal access for this purpose 
 jcnichols I hope this helps to get this corrected. Also, the client wants to know to whom they can send my bill. ;-)

connex · Answer

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 
 Failing SQL Statement: (reduced to its simplest form) 
 
 SELECT IM_ItemTransactionHistory.ReferenceDate FROM IM_ItemTransactionHistory IM_ItemTransactionHistory WHERE (IM_ItemTransactionHistory.ItemCode='005') ORDER BY IM_ItemTransactionHistory.ReferenceDate

This is what my 64-Bit testing showed: 
 
 If I removed the C/S ODBC parameters: RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222; from the connection string it works with 64-Bit. 
 Eliminating fields in order to isolate a problem field value proved inconclusive
 
 If I eliminated fields from the query down to just 'ReferenceDate' it sill failed 
 If I replaced 'ReferenceDate' with 'TransactionDate' it worked - however... 
 If I put 'ReferenceDate' back into the SQL statement it still worked. 
 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. 
 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.

What I learned:
 
 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) 
 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) 
 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.

Sage 100

"[ProvideX][ODBC Driver] Internal Error"

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING