Sage 100

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

Is there any ultimate resolution to this?  Still an issue in v2023.

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

Dan's suggestion of removing Order By from the query fixed it for the one problem workbook a customer had that was randomly throwing the error. 

Is there any ultimate resolution to this?  Still an issue in v2023.

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

I removed the sort as well.  Need to zero out the cache in BOTH places noted and test that option

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------


Original Message:
Sent: 11-01-2023 09:48
From: Eric Lunceford
Subject: Sage v2021 64 bit ODBC Puzzler

Dan's suggestion of removing Order By from the query fixed it for the one problem workbook a customer had that was randomly throwing the error. 

------------------------------
Eric Lunceford
First Mate Business Solutions
Oklahoma City, OK
877-880-8960


Original Message:
Sent: 10-31-2023 13:31
From: Jeff Schwenk
Subject: Sage v2021 64 bit ODBC Puzzler

Is there any ultimate resolution to this?  Still an issue in v2023.

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444


Original Message:
Sent: 04-22-2022 02:27
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

I have attached a file that will encounter the "Internal Error" in the earlier mentioned 64-Bit Excel configurations without the workarounds. It requires Sales Order and a similar query does not fail in A/R. Does it fail in your environments?

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-21-2022 23:59
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

The only Sage 100 version I've tested that works with a 64-Bit App is Adv 2019. I have tested these non-C/S ODBC configurations.

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-21-2022 19:56
From: Jeff Schwenk
Subject: Sage v2021 64 bit ODBC Puzzler

Interesting.  Is there a way to determine by release number if the installed version of MS365 is the culprit or is it easier to just try one of the fixes outlined?

------------------------------
Jeff Schwenk
Bottomline Software, Inc.


Original Message:
Sent: 04-21-2022 19:34
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

This is has now been reported to me in absence of the C/S ODBC configuration and could be @Jeff Schwenk's issue. It's happening now with 64-Bit applications (i.e. Excel) and the Sage 100 32/64-Bit ODBC driver and any size tables. This appears to be due to a recent change in MS Office 365 or Windows because this has been working unchanged for years before now whereas Sage 100 has not changed in my tested enviornments. I've experimented with this and found a couple for work-arounds that are short of reverting to 32-Bit Excel. Hint: Only queries with a sort (i.e. "ORDER BY" SQL clause are failing)

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

​

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-16-2022 03:02
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

I had this issue in version 2019 (6.10.0 & 6.10.5) with 64-Bit Excel/Power Query and the 32/64-Bit ODBC driver. C/S ODBC was enabled and I was running against large history tables, but it was intermittent. Either turning off C/S ODBC or using 32-Bit Excel worked around it.

------------------------------
Dan Burleson
Software Consultant
Connex Software

Change to Power Query. Problem solved. I also worked around the issue by removing the sort from the query and adding it at the resulting Excel table level. Funny, I just had this problem again today on an SPC install.

I removed the sort as well.  Need to zero out the cache in BOTH places noted and test that option

Dan's suggestion of removing Order By from the query fixed it for the one problem workbook a customer had that was randomly throwing the error. 

Is there any ultimate resolution to this?  Still an issue in v2023.

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

@Dan Burleson - Excellent gumshoe work done in the Community Hub referenced above.  Are you saying that if I replace the DSN reference with this (driver={MAS 90 4.0 ODBC Driver} the original query will work with the sorts?

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------


Original Message:
Sent: 11-02-2023 02:29
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

Change to Power Query. Problem solved. I also worked around the issue by removing the sort from the query and adding it at the resulting Excel table level. Funny, I just had this problem again today on an SPC install.

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 11-01-2023 16:02
From: Jeff Schwenk
Subject: Sage v2021 64 bit ODBC Puzzler

I removed the sort as well.  Need to zero out the cache in BOTH places noted and test that option

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444


Original Message:
Sent: 11-01-2023 09:48
From: Eric Lunceford
Subject: Sage v2021 64 bit ODBC Puzzler

Dan's suggestion of removing Order By from the query fixed it for the one problem workbook a customer had that was randomly throwing the error. 

------------------------------
Eric Lunceford
First Mate Business Solutions
Oklahoma City, OK
877-880-8960


Original Message:
Sent: 10-31-2023 13:31
From: Jeff Schwenk
Subject: Sage v2021 64 bit ODBC Puzzler

Is there any ultimate resolution to this?  Still an issue in v2023.

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444


Original Message:
Sent: 04-22-2022 02:27
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

I have attached a file that will encounter the "Internal Error" in the earlier mentioned 64-Bit Excel configurations without the workarounds. It requires Sales Order and a similar query does not fail in A/R. Does it fail in your environments?

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-21-2022 23:59
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

The only Sage 100 version I've tested that works with a 64-Bit App is Adv 2019. I have tested these non-C/S ODBC configurations.

Office 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit / Windows Pro 11 21H2 Build 22000.613
   Sage 100 Adv v2019 Works
   Sage 100 Adv v2020 Fails
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20178) 64-bit / Windows Pro 10 1909 Build 18063.1556
   Sage 100 Adv v2019 Works
   Sage 100 Std v2021 Fails
Office 365 MSO (Version 2203 Build 16.0.15028.20152) 32-bit / Windows Pro 10 21H2 Build 19044.1645
   Sage 100 Std v2021 Works

I'll attach a sample Excel file that failed with "Internal Error" when refreshed with 64-Bit Excel in the above configurations. Hopefully others will add to the list.

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-21-2022 19:56
From: Jeff Schwenk
Subject: Sage v2021 64 bit ODBC Puzzler

Interesting.  Is there a way to determine by release number if the installed version of MS365 is the culprit or is it easier to just try one of the fixes outlined?

------------------------------
Jeff Schwenk
Bottomline Software, Inc.


Original Message:
Sent: 04-21-2022 19:34
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

This is has now been reported to me in absence of the C/S ODBC configuration and could be @Jeff Schwenk's issue. It's happening now with 64-Bit applications (i.e. Excel) and the Sage 100 32/64-Bit ODBC driver and any size tables. This appears to be due to a recent change in MS Office 365 or Windows because this has been working unchanged for years before now whereas Sage 100 has not changed in my tested enviornments. I've experimented with this and found a couple for work-arounds that are short of reverting to 32-Bit Excel. Hint: Only queries with a sort (i.e. "ORDER BY" SQL clause are failing)

1. If the Excel workbook uses MS Query, use either of the following two remedies to edit the Connection properties (Data menu & select "Queries & Connections" command)
   1. Method 1: Remove the ORDER BY clause in the Command Text (aka SQL query). This may be undesirable because the sort would need to be done outside the query.
   2. Method 2: Remove the "Cache Size" parameter in the Connection Strings in both places (yes, really in two places)
      
      1. Set the Cache Size parameter to zero ("0") in the ODBC control panel for the DSN name used by the report.
      2. Open the Excel file with the failing query:
         1. On Excel's Data tab, click "Queries & Connections" to expose the panel of queries on the right edge of the current worksheet.
         2. Right-click the failing query that draws from the Sage 100 DSN and select "Properties"
         3. In the Connection String field, remove everything after the DSN name (e.g. everything after "DSN=SOTAMAS90") and click "OK" (clicking "OK" will restore the Connection String from the ODBC DSN and refresh the query)
         4. Check each additional failing query and make sure that the Cache Size parameter is no longer present in its Connection String and remove it if it is found.
      3. The "CacheSize=0" setting will survive a subsequent Workstation Setup repair, but not if any Sage 100 Workstation is subsequently installed (including after an uninstall of the same version).
2. If the Excel workbook uses Power Query, use either of the following two methods to remedy the issue:
   1. Method 1: Remove the "ORDER BY" Clause:
      1. If an "ODBC.Query" is used in the Power Query Source step, remove the ORDER BY clause (usually step 1) and add/insert an equivalent sort step in 2nd position.
      2. If an "ODBC.Query" is not used in the Power Query Source step such as when a "ODBC.Datasource" function is used instead then this "Internal Error" is not the error addressed here.
   2. Method 2: Simply change the Cache Size parameter in the DSN used by the query using either the 32-Bit or 64-Bit ODBC control panel. Because Power Query connections do not copy the parameters into a connection string like MS Query does there are no further steps. 

​

------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 04-16-2022 03:02
From: Dan Burleson
Subject: Sage v2021 64 bit ODBC Puzzler

I had this issue in version 2019 (6.10.0 & 6.10.5) with 64-Bit Excel/Power Query and the 32/64-Bit ODBC driver. C/S ODBC was enabled and I was running against large history tables, but it was intermittent. Either turning off C/S ODBC or using 32-Bit Excel worked around it.

------------------------------
Dan Burleson
Software Consultant
Connex Software