Open Forum

Multi Company question

  • 1.  Multi Company question

    Posted Jul 25, 2019 06:05 PM

    We have SL set up for use by multiple companies, most of which we no longer use nor do we want or need to access them.
    Are there any adverse affects to removing them from the Company Maintenance screen?
    And then since we no longer use the DB's that these companies are set up in, can we delete the DB's without adverse consequences?

    Thanks in advance.
    Janelle Hoyt

    Janelle Hoyt
    Eberl Claims Service, LLC
    Lakewood CO

  • 2.  RE: Multi Company question

    Posted Jul 26, 2019 09:11 AM
    In my experience, removing a database from the company table can be quite complicated.     The problem is that every database carries a record of all of the other databases, as well as GL accounts for all of those databases.    So you start to get errors if you haven't found all of the places those exist in the various databases.   The symptom I had was that after removing the records in company and domain, I couldn't log in at all due to errors.

    Depending on your version of SL, starting in V2015 there is a flag to mark a database as inactive on the company table.    That might be a first start so that the retired databases are not on the list of companies users can log into.

    You may want to get advice on this one from tech support.   I was able to do it in V2011, but I did it over a couple of weekends and found that simply removing the database from company and domain was not sufficient.   The first weekend I couldn't get logged back into any database, and ended up restoring the records in domain & company.    I think I remember running "Synchronize all databases" and even re-ran setting Windows Authentication before successfully removing a database I had created in error.

    I'm just saying be careful and do it when you have some time for troubleshooting afterward.

    Best regards,

    Gail J-N

    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC

  • 3.  RE: Multi Company question

    Posted Jul 30, 2019 03:35 PM
      |   view attached
    Hello Janelle,
    We at Plumbline actually have a product that was created for this reason.  It is called DB Merge Plus.   This product not only let's you merge databases together but it allows you to pull companies out of current databases just like what you are asking to do above.   I have attached the user guide to this for you to review.  If you have any other questions about the product please feel free to reach out to me.

    Valerie Borak
    Plumbline Consulting
    Findlay OH


  • 4.  RE: Multi Company question

    Posted Jul 30, 2019 05:00 PM
    Thank you Valerie!
    I want to be clear.  All of the separate companies have their own application database and have not been used at all since 2015, but they all use the same SL System Database.  So I am trying to understand how this utility would help us when all we really want to do is be able to remove these obsolete databases.

    Thank you again.

    Janelle Hoyt
    Eberl Claims Service, LLC
    Lakewood CO

  • 5.  RE: Multi Company question

    Posted Jul 31, 2019 01:44 AM
    Hi Janelle,

    As long as these unwanted companies are in their own databases that don't contain other companies, it should be a fairly simple process.

    1. Backup all databases and plan to keep the deleted company DBs and the system DB forever
    2. Delete these companies from company table in the system db
    3. Delete references to these companies from intercompany table in the system db
    4. Delete the databases from domain table in the system db
    5. In Database Maintenance Utility, run Update Views and Synchronize All Ownership and Security

    To be safe, I'd highly recommend running through these steps in a test environment, then do some testing (such and entering, releasing and posting transactions), before doing it in production.   Depending on your version and security setting (Windows vs SQL auth), there may be more things to watch out for.

    Hope this helps,

    John Lian
    Principal Consultant
    The ERP Group, LLC

  • 6.  RE: Multi Company question

    Posted Jul 31, 2019 10:13 AM
    Hi John,

    Thank you so much!  This is exactly what I was looking for some step by step direction.

    I will definitely test this in a test environment first.

    Have a great day!

    Janelle Hoyt
    Eberl Claims Service, LLC
    Lakewood CO

  • 7.  RE: Multi Company question

    Posted Jul 31, 2019 06:32 AM

    It is possible to remove databases as I have in the past but there are a few things to consider.  If there is company specific access to functions by users, I would clean that up first.  If you have reporting such as Management Reporter, there are links in there and possibly the reporting trees that will need to be cleaned up.  If using Windows Authentication, make sure the user mappings in SQL are addressed as to permissions and access and the backups that may be in place are not looking for these databases.

    I am not a fan of deleting databases but there is an easy way to unclutter the Dynamics SL system.  In the company table in the system database, there is a field "Active" that basically makes the company available for use.  The default value is 1 but if you run a SQL update statement like with replacing the XXXXX with your company id:
    Update Company set active = 0 where cpnyid = 'XXXXX'
    you can remove the companies from access and visibility from within Dynamics SL.

    Also, if you decide to remove the company, check that company table to make sure no other company is using the CpnyCOA and CpnySUB from the company you are deleting.
    select * from company

    David Callery
    Coe & Company, LLC
    Jefferson LA

  • 8.  RE: Multi Company question

    Posted Jul 31, 2019 10:15 AM
    Thank you David!

    This is also helpful with your step by step directions and it is much appreciated.

    Have a great day!

    Janelle Hoyt
    Eberl Claims Service, LLC
    Lakewood CO