"[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.

  • 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

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

    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. I 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.

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

Children
No Data