Sage 100

 View Only

  • 1.  SQL Excel Queries

    Posted 04-23-2020 18:18
    I recently upgraded a client from v2016 Advanced to the 2019 SQL edition. Come to find out they had a number of Excel queries using the SOTAMAS90.dsn driver that they neglected to mention. 

    As you can image, the Excel queries no longer perform since sotamas90 driver does not work in SQL.

    Does anyone have any thoughts or best practices on converting them. I suspect that I am going to have to re-write each query using a SQL ODBC driver. Also, I want to make sure that any Excel reports do not write back to SQL.

    Any ideas or comments are welcome.

    Thank you,
    Michael


    ------------------------------
    Michael Davis
    Warren Averett Technology Group
    Birmingham, AL
    ------------------------------


  • 2.  RE: SQL Excel Queries

    Posted 04-23-2020 18:49
    Only give the users read permission to SQL.
    You may be able to copy / paste the new SQL connection string in to existing queries.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 3.  RE: SQL Excel Queries

    Posted 04-24-2020 09:38
    From Excel use the Data>From Other Sources>From SQL Server.  You will need to know the SQL Instance name and I always use the SQL "sa" for the authentication.  From there you will be able to select the table(s) you need to pull into Excel.  You do not need to use the MAS_Reports like Crystal requires.  Hope this helps.

    ------------------------------
    Bruce Kern
    SWK Technologies Inc.
    ------------------------------



  • 4.  RE: SQL Excel Queries

    Posted 04-24-2020 12:42
    I had someone with a ton of MS Access where they moved from Advanced to Premium and we created a separate ODBC access to SQL ( read only ) and they were able to swap the source pretty easily. The field names all stayed the same. It took a few hours of toying but once they got the right combination of how to update they went through it pretty fast from that point on.

    I'd say the effort is worth it if they are doing any type of external work to Sage data and speed is a concern.

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------



  • 5.  RE: SQL Excel Queries

    Posted 04-25-2020 07:55

    Thank for the tips everyone. This was not as bad as they first said. Once we did the first one report, it was a simply a matter of copying the DSN information in the query to the other MS Query Excel sheets. The command text was the same both PVX and SQL.

     

    The only difference was the DSN connection string. So instead of copying from odbc32.dsn, we were able to copy the string from the first Excel sheet we connected to SQL.

     

    The rest was a simply copy and paste process.

     

    I did setup a separate SQL user with dbreader rights only too.

     

    Thank you

    Michael

     

     

    Michael H . Davis, CPA.CITP, MCSE

    (D)

    205.313.4734

    (S) 

    205.313.4700

     



    ------Original Message------

    I had someone with a ton of MS Access where they moved from Advanced to Premium and we created a separate ODBC access to SQL ( read only ) and they were able to swap the source pretty easily. The field names all stayed the same. It took a few hours of toying but once they got the right combination of how to update they went through it pretty fast from that point on.

    I'd say the effort is worth it if they are doing any type of external work to Sage data and speed is a concern.

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------


Related Content