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

  • 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.

  • 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

  • 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. 

  • 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 

  • 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.