x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 5 replies
  • Subscribers 75 subscribers
  • Views 3726 views
  • Users 0 members are here
Browse Forums
Announcements
78
topics
166
replies
General Discussion
5k
topics
16k
replies
Installation and Upgrades
978
topics
2k
replies
Inventory, Projects, and Departments
337
topics
1k
replies
Payables and Receivables
1k
topics
4k
replies
Payroll
1k
topics
3k
replies
Reconciliation and Banking
315
topics
992
replies
Reporting and Analytics
206
topics
397
replies
Soutien Technique - Français
626
topics
1k
replies
Year End Activities
223
topics
595
replies
mfanning2010

Sage 50 CA Integration using MySQL ODBC issue with Ports

Posted By

Hello,

We are working to integrate data from Sage 50 CA 2017 (General Ledger transactions mainly) to a Microsoft SQL Server table as a staging place to then bring into another application. We are able to this successfully by creating a MySQL ODBC (5.a driver) connection to the simply database and then using SQL statements and Openquery functions to insert the data into the staging table. This part works great, but what happens is that it seems that either when the end user is either restarting their machine or switches between their two databases in Sage the port number the Simply database is running on switches from one port to another so any scheduled integration fails since the Linked Server we created to the ODBC driver cannot connect to the MySQL database.

Changing the port in the ODBC driver corrects the issue, but how can we prevent this from happening? Has this happen to anyone else?

I found this link which describes the firewall access to allow connections and the port range described (13540-13545) I believe is our issue. The ODBC driver will connect just fine with 13540 one day, but the next it cannot so we change to 13541 and it works again. 

There are two end users of Sage 50 and two databases so is there a unique port per user or could it change if they switch between the databases?

https://support.na.sage.com/selfservice/viewdocument.do?noCount=true&externalId=10385&sliceId=1&cmd=&currentmsID=MS_Customer&docLink=true&isDocLink=true&languageId=&ViewedDocsListHelper=com.kanisa.apps.common.BaseViewedDocsListHelperImpl

 Thank you for any assistance, 

Matthew Fanning

  • 0

    When you are using software that requires the MySQL database to be opened already, you are stuck with this scenario you are experiencing.  There is one port per company data file.

    Sage is aware of this issue and put in the Sage 50: Last Opened. User DSN.  By definition, this DSN points to the last Sage 50 file opened on that computer (not necessarily the highest port number).  Technically if you wish, you can just close the file you wish to report on, open it again and always use the Last Opened DSN.

    If you report on company 1 and the user last opens company 2, then Last Opened points to company 2.

    However, if company 1's datafile is called ABC.sai, then there is also a Sage 50: ABC.sai User DSN.  This will always have the port to this database.  It is updated each time the file is opened.  Assuming you are only reporting on the one company file are you not using this one?

    In the morning if all users always open the files in the same order, you will never have a problem if you have created your own DSN.

    That link describes firewall blocking problems and how to get around it.  It does not explain how the ports are used and assumes the users will only have a maximum of 6 company files open simultaneously.

  • 0 in reply to Richard S. Ridings

    Hi Richard,

    Thank you for the information!

    Now we looked at the ConnectionManager.ini C:\Users\Public\Documents\Sage Software\ConnectionManager\ which controlled the range of the ports. Is it possible to change it so the start and end port is the same port so effectively it would always be the same or is this ill-advised? I can imagine if the same user wanted to have Sage open and connected to both databases at the same time that would cause problems, but I am not sure if Sage allows this. 

    Matthew Fanning

  • 0 in reply to mfanning2010

    I don't know if Sage will allow it or not (I wouldn't know who to ask) but I wouldn't do it because you can't open two Sage 50 files simultaneously if it only allows one.  I would not play with the program setup.

    What happened when you tried what I suggested above while you changed the order of file opening?

  • 0 in reply to Richard S. Ridings

    The issue is occurring with a client we are providing services to so we have not been able to try it yet, but definitely will. 

  • 0 in reply to Richard S. Ridings

    Thank you again for the information. Things see to be connecting better with the understanding that the order of opening the companies is what matters when it comes to the port assignment. 

*Community Hub is the new name for Sage City