Direct DB Connections via Connection Manager | Changing Port #s

Hi folks,

I have a client that uses Sage 50 (2018.1 + Connection Manager v7.0).  They host Sage on a MS Azure server.  They have 6 Companies (DBs) that I'd like to be able to connect to so that I can pull some high-level data into a custom reporting tool we are creating.

I've come close to the right setup: I've setup a user with 3rd Party Products access (with shortened password). 

With MySQL Workbench I can make a successful connection to: the.server.ip.addy:13541 etc by looking up the port assigned to each company via the Sage 50 application.

I thought I was in the clear, but as some of you know the port numbers are dynamically assigned, not hard coded as discussed here in the past:

https://sagecity.na.sage.com/support_communities/sage_50_accounting_ca/f/sage-50-ca-third-party-development-partners/66504/is-there-a-way-to-keep-connect-to-the-database-while-sa-is--not--running

https://sagecity.na.sage.com/support_communities/sage_50_accounting_ca/f/sage-50-ca-third-party-development-partners/66573/odbc-connections-port-questions/200592#200592

I haven't been able to find any recent discussions on this topic.  Is my assumption correct that the situation hasn't changed?  I figure I can't be the 1st person to want to be able to do this type of connection to multiple companies on the same server via Connection Manager.  

I see the folks over at Logic IM are charging $100 for the KeepAlive service that is mentioned in a few older threads:

https://www.logicimtech.com/en/Products/KeepAlive

I'm hoping to be able to touch base with someone who is running this type of setup.  Even if KeepAlive is the only way, that is fine, I'd just like to be able to bounce some questions off anyone doing it that way.

thanks in advance!

Jason

JGR Tech

Parents
  • 0

    Would it be feasible to use the SDK to make a connection, and pass the port number to your reporting tool?  

    I just tested one of the built-in Excel reports (journal entry listing) on a closed (local) copy of a database and it was able to start the connection manager, start the MySql database, extract the data, place it into an Excel sheet, then close down.  It worked the same on an open database with 12 active users.

    As far as I understand, KeepAlive is intended for doing a series of reports more efficiently - it will keep the database open so that the ports don't change and you don't have to keep logging in or waiting for the Connection Manager to fire up the database daemon.  I'm not sure if it is configurable to automatically shut down to allow backups, or to open a new connection to a closed database, so it may not be what you want to run reports overnight.

    Your second link above is to a post by one of the owners / programmers at Logicimtech.  They make a reporting tool as well, and as far as i know it can connect to any number of Sage 50 instances automatically.  So it must be possible.

  • 0 in reply to RandyW

    Thanks for the reply Randy!

    I've read up a bit on the SDK.  I'm not much of an app coder (vb.net etc) but if that was the only option I'm sure I could figure it out.  

    So far, I've been lucky building out the data warehouse as I've been going from SQL Server to SQL Server with the other systems using the command line BCP utility.  Sage has been a bit more complicated (so far).

    My Sage export plans are pretty simple, literally just need to be able do a SELECT * FROM these tables in the 6 Company DBs: tAccount, tjourent, tjEntAct, tVendor, tVenTr, tVenTrDt.

    Jason

  • 0 in reply to JasonR

    Hi Jason,

    The SDK is a free download, and includes a diagram of some of the table relationships DataRel.PDF that is helpful in puzzling out the naming conventions (or lack thereof) in the data dictionary. 

    The VBA code in the included Excel sheets is another good starting place, simple SELECTs into Excel are fairly straightforward, and you could also program in the saving of a CSV dump with a specific filename, kicked off from a command line.  The side-by-side balance sheet included with Sage 50 would be accessing 3 of those (current year only) tables. 

  • 0 in reply to RandyW

    Randy,

    Thanks for the info.  I have the hefty SDK docs printed off and have gone through them.  I haven't jumped in the VBA/.NET examples yet so I'll do that next.  I noticed some wording in the docs about 32-bit compiles etc which dissuaded me originally from jumping in deeper since our systems are all 64bit.  They also use an older .NET version compared to what is in use on the client site at the moment.

    Jason 

  • 0 in reply to JasonR

    JasonR said:
    I have the hefty SDK docs printed off and have gone through them.  I haven't jumped in the VBA/.NET

    The SDK requires Visual Studio, and comes with sample files in Visual Basic, C#, and C++.   The C# example is very complete, just uncomment the modules in Main.   The C++ and Visual Basic sample files do very little, but in theory the C# dot-NET code should be easily ported.

    The Excel Sheet reports, on the other hand, use the non-compiled Visual Basic for Applicatiions (VBA) and only require that you have Excel and Sage 50 installed.

    JasonR said:
    I noticed some wording in the docs about 32-bit compiles etc which dissuaded me originally from jumping in deeper since our systems are all 64bit.

    Sage 50 is 32 bit, and all the DLLs are 32 bit and cannot be accessed from a 64 bit program.  Windows 64 bit workstations run a 32 bit 'Windows on Windows' subsystem / virtual machine so there are more moving parts and 32 bit programs run a little slower than they would on a 32 bit workstation.  Sage 50 appears to be nowhere near the performance limits of a 32 bit operating system (i.e. 2 terabyte files, 4 GB RAM), so I would think it unlikely that Sage will port it until Microsoft stops providing a way to run 32 bit programs on their latest OS.

    JasonR said:
    They also use an older .NET version compared to what is in use on the client site at the moment.

    As far as I know, the .NET version is a minimum system requirement only, and newer versions in the same series (2.x, 3.x, 4.x) will work with older code.  A system using VBA in Excel will avoid any .NET versioning issues.  

  • 0 in reply to RandyW

    Thanks Randy, do the Excel Sheets reports come with the sdk?  I'm not seeing them in what I downloaded.  Having trouble getting the C# examples probably opened in my environment so going to take a look at the Excel stuff.

  • 0 in reply to JasonR

    JasonR said:
    do the Excel Sheets reports come with the sdk?

    Hi,

    I'm not sure they're in every Edition, usually they're installed with the Sage 50 workstation software, in the Reports folder, and can be found on the Reports menu.

  • 0 in reply to RandyW

    Still tackling this connectivity issue.  I've copied the VB.NET example and am trying to run this:

    szUsername = "myUsername"

    szPassword = "myPassword"

    szHost = "the.server.ip.address"

    szPort = ""  --> tried blank, 13540, 13531 etc

    szFileDir = "E:\Simply\CompanyFolder\Company Corporation.saj"

    dbError = dbClient.GetConnectionInfo(szFileDir, szHost, szPort)

    The error I get back from dbClient.GetConnectionInfo is "Error_ConnectionMgr_None".

    Perhaps I'm not understanding how the connection works.  Our server is an Azure VM so I don't have access to szFileDir from my local PC.  It's unclear from the SDK docs if you need to have access to this from the client PC or if it's passed to the ConnectionManager and dealt with up on the server to return the port #.  I'm running this code from my local PC.

    Having said all this, I can connect to one of the company DBs via MySQL Workbench using the.server.ip.address:XXXXX - provided I look up the port number of the company DB I want to connect to.

    So I must be close.

    I've contemplated just making direct MySQL connections starting with port 13540, reading something from the DB that I connect to that would indicate which one I'm in (company name etc), doing my work (exporting data) and moving on to the next port to connect to the next DB.  We have 6 to connect to...

  • 0 in reply to JasonR

    Yes, that saj file is a config file.

    So in this case, that is a standard windows file in a standard file location. The users running sage 50 when they launch sage ALSO will have use of that .saj file, and they have use via a standard windows resolved path name.

     So like any pdf, or any other kind of file that you specify a full path name to, it is assumed that users have full rights and use of that file in a standard windows folder.

    So when users launch sage, they must have full use of that .saj file that is sitting in standard windows folder. Without use of that file, then you can't launch sage. And same goes for your software - you launch your software, then again you need use of that file just like any sage user does.

    Regards,

    Albert D. Kallal

    Edmonton, Alberta Canada

    [email protected]

  • 0 in reply to Albert D. Kallal

    Thanks for that explanation.  Sage 50 is hosted on an Azure VM and the code doing the export won't be on the same server so that type of connection is out for me.

    What I've done for the time being is written a VB.net app to cycle through a list of ports:

    {"13540", "13541", "13542", "13543", "13544", "13545"} etc...

    ...and attempting to connect.  When it does connect I check the Company Name...

    "SELECT sCompName FROM tCompany"

    ...Then I know what DB I'm in since the company name is unique in my case.  Then I just do my SELECT * from the 5 tables I need and dump the data to file and move on.

  • 0 in reply to JasonR

    JasonR said:
    Our server is an Azure VM so I don't have access to szFileDir from my local PC.

    Sage 50 appears to:

    seems to use an SMB Windows file connection to the file with the .sai extension to get the server name information, the Windows name of the server share, and the location of the file folder with the matching .saJ extension. 

    JasonR said:
    It's unclear from the SDK docs if you need to have access to this from the client PC or if it's passed to the ConnectionManager and dealt with up on the server to return the port #.  I'm running this code from my local PC.

    You have to be able to at least open the .sai file on the server, i.e. from Notepad, and the 'E:\' should show with a resolveable UNC path name if you go to a command prompt and run 'Net Use'.  You should probably be able to ping, but definitely will have to be able to telnet to that server name on port 13540.

    The client (or SDK passes information from the file connection back to the Connection Manager service on the server, which opens a port to the MySQL dbms then the Connection Manager passes the port number back to the workstation.

    Is the Sage 50 client is actually RUNNING on the workstation, or on the Azure VM host?

  • 0 in reply to RandyW

    Thanks for the info.

    The folks here are using Azure RemoteApp so they are essentially RDPing up to the Azure VM host.  My nightly export program won't have that luxury since it will be running unattended.   

Reply Children
  • 0 in reply to JasonR

    A redirected drive probably wouldn't be able to find the same connection info as a drive mapping.  Probably the only way to reliably run the SDK code is under the same conditions that the Sage 50 workstation client can run under. 

    Any app built with the SDK would more or less be a very stripped-down automated workstation client, and may only be able to run on the RDP server.