"[ProvideX][ODBC Driver] Internal Error"

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.

Parents
  • 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.

Reply
  • 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.

Children
  • Hi.

    "will occasionally work"  Grimacing.  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

    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:
    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. Blush

  • 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