x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 23 replies
  • Answers 3 answers
  • Subscribers 112 subscribers
  • Views 5515 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
44
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
amazingsol

Sage ODBC with Sage 100 V2020

SUGGESTED
Posted By

I am upgrading a client to Sage 100 V2020 and am running into issues with some Excel spreadsheets that have been working for years in prior versions of Sage 100.  In V2020 the client upgraded their Excel version from 2013 to Excel 365 Enterprise 64 bit installed locally on the server.  I installed the 64 bit Sage 100 ODBD driver and I can connect to the Sage 100 database from inside Excel using Microsoft Query and download the data to Excel.  However, if I try to sort the query by Customer No, I get a Microsoft Error when I try to refresh the query:

                                           

I have admin rights on the server that I'm running the queries on.  Every old Spreadsheet query that involves sorting by the Customer fails.  If I remove the sorting, it refreshes fine.  I'm wondering if Providex has an update ODBC driver that we need to install for Excel 365 Enterprise.  I'm using Providex ODBC driver 64 bit v4.40.0004 currently.  Any thoughts would be appreciated!  Thanks!

Parents
  • 0

    Hi, Version v4.40.0004 of the ODBC driver is current. Can you post your SQL query and I'll take a look.

    Thanks 

    John Nichols

    Sage

  • 0 in reply to jcnichols

    John,

    From the comments and testing of several people in this topic and my own testing, it appears that the Sage ODBC 64 bit driver has got some issues doing simple tasks like ORDER BY of certain fields in a query.  What do we need to do to get Sage to look into this problem and hopefully correct the 64 bit driver?  I had to switch my client to the 32 bit version of Excel 365 in order to solve my problems.  Once I did that, everything now works without a problem.  Thanks, Mark  

  • 0 in reply to amazingsol

    Hi Mark,  I'll revisit the issue being reported. I am unable to replicate the issue or the issues reported by others. . Can you post your x64 and x86 connection string?    Thanks John

  • 0 in reply to jcnichols

    John,  Here's the connection string:

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

     The problem has never been making the connection.  I could always connect to the database.  The problem is trying to use ORDER BY in the query:

    SELECT AR_InvoiceHistoryDetail.InvoiceNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryDetail.ItemType, AR_InvoiceHistoryDetail.ItemCodeDesc, AR_InvoiceHistoryDetail.QuantityShipped, AR_InvoiceHistoryDetail.UnitPrice, AR_InvoiceHistoryDetail.UnitCost, AR_InvoiceHistoryDetail.ExtensionAmt, AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryHeader.BillToName, AR_InvoiceHistoryHeader.InvoiceType, AR_InvoiceHistoryHeader.InvoiceDate, AR_InvoiceHistoryHeader.SalespersonNo

    FROM AR_InvoiceHistoryDetail AR_InvoiceHistoryDetail, AR_InvoiceHistoryHeader AR_InvoiceHistoryHeader

    WHERE AR_InvoiceHistoryDetail.InvoiceNo = AR_InvoiceHistoryHeader.InvoiceNo AND AR_InvoiceHistoryDetail.HeaderSeqNo = AR_InvoiceHistoryHeader.HeaderSeqNo AND ((AR_InvoiceHistoryDetail.ItemType='1') AND (AR_InvoiceHistoryHeader.InvoiceDate>=? And AR_InvoiceHistoryHeader.InvoiceDate<=?))

    ORDER BY AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryHeader.InvoiceDate

    If I remove the first field in the ORDER BY clause the query works.  Put it back in and the query fails with an internal Excel error.  These are queries that we've been using for years with older versions of Excel and Sage 100.  Unfortunately, my client went live with Sage 100 2020 last weekend so I had to remove Excel 365 64 bit and installed Excel 365 32 bit instead.  Now every query that we have been using in the past is now working fine with no changes other than pointing to the new server.  I won't be able to show you more examples of the problems we were having with the 64 bit ODBC driver but others had similar problems with the ORDER BY function.  Thanks for looking into this!  I'd like to be able to use Excel 64 bit in the future.

Reply
  • 0 in reply to jcnichols

    John,  Here's the connection string:

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

     The problem has never been making the connection.  I could always connect to the database.  The problem is trying to use ORDER BY in the query:

    SELECT AR_InvoiceHistoryDetail.InvoiceNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryDetail.ItemType, AR_InvoiceHistoryDetail.ItemCodeDesc, AR_InvoiceHistoryDetail.QuantityShipped, AR_InvoiceHistoryDetail.UnitPrice, AR_InvoiceHistoryDetail.UnitCost, AR_InvoiceHistoryDetail.ExtensionAmt, AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryHeader.BillToName, AR_InvoiceHistoryHeader.InvoiceType, AR_InvoiceHistoryHeader.InvoiceDate, AR_InvoiceHistoryHeader.SalespersonNo

    FROM AR_InvoiceHistoryDetail AR_InvoiceHistoryDetail, AR_InvoiceHistoryHeader AR_InvoiceHistoryHeader

    WHERE AR_InvoiceHistoryDetail.InvoiceNo = AR_InvoiceHistoryHeader.InvoiceNo AND AR_InvoiceHistoryDetail.HeaderSeqNo = AR_InvoiceHistoryHeader.HeaderSeqNo AND ((AR_InvoiceHistoryDetail.ItemType='1') AND (AR_InvoiceHistoryHeader.InvoiceDate>=? And AR_InvoiceHistoryHeader.InvoiceDate<=?))

    ORDER BY AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryHeader.InvoiceDate

    If I remove the first field in the ORDER BY clause the query works.  Put it back in and the query fails with an internal Excel error.  These are queries that we've been using for years with older versions of Excel and Sage 100.  Unfortunately, my client went live with Sage 100 2020 last weekend so I had to remove Excel 365 64 bit and installed Excel 365 32 bit instead.  Now every query that we have been using in the past is now working fine with no changes other than pointing to the new server.  I won't be able to show you more examples of the problems we were having with the 64 bit ODBC driver but others had similar problems with the ORDER BY function.  Thanks for looking into this!  I'd like to be able to use Excel 64 bit in the future.

Children
  • 0 in reply to amazingsol
    SUGGESTED


    Hi Mark,
    I'm still researching but I'm not convinced that the issue is with the Sage 100 ODBC x64 driver.
    I can run the above SQL statement in other x64 applications without any issue (i.e. 2020 Crystal Reports, ODBC query tools), the SQL statement runs
    using MS Query in in Office 365 x64 Excel without issue. The SQL statement fails as you described if I use the MS Query wizard.

    I'll follow up to this thread when I'm able to provide additional information.

    Thanks John

  • 0 in reply to jcnichols

    Thanks, John!  I use the MS Query wizard in Excel all the time and have never had a problem before.  Glad to hear that I'm not imagining things.  Kevin in a previous comment on this thread said that he was able to duplicate the problem with an ODBC link to a SQL server:

      • Offline 6 days ago in reply to amazingsol

        I have a SQL linked server set up using the 64-bit ODBC driver, and order by InvoiceNo works, but not CustomerNo.  Odd, but this confirms the issue is not specific to Excel.  (I have no further insights).

        Thanks again, John!

        Mark

    *Community Hub is the new name for Sage City