Merge two Dynamics into one dynamics but not companies

  • Merge two Dynamics into one dynamics but not companies

    Posted by DSC Communities on January 26, 2017 at 1:16 pm
    • NaveedAli Ali

      Member

      January 26, 2017 at 1:16 PM

      Hi,

      I want to Merge two Dynamics into one dynamics but not companies..

      for.e.g.

      One Dynamics with Company A and Second Dynamics With Company B,

      after merging One Dynamics with Company A and Company B.

      Let me know what steps and Pro or Cons i need to consider..

      Regards..

      Naveed Ali

      ——————————
      Naveed Ali
      Technical Lead
      Evincible Solutions LLC
      Houstan TX
      8325866423
      naveedali.dewa@gmail.com
      ——————————

    • Rob Klaproth

      Member

      January 26, 2017 at 1:44 PM

      You can not merge two Dynamics Databases.  So, the easiest way to accomplish this is to decide which one you want to keep.  Say you’re going to keep Dynamics for Company A and you want to bring company B over to Dynamics A.
      What you will do is launch GP Utilities on Dynamics A and “create a company”, give it exactly the same ID and name as Company B currently is today.   Then when you are done creating the company you will want to restore Company B onto the DYNAMICS A server.    After you do this, you will then need to run the script against the Company B database on the new Dynamics A server:
      if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
      declare @Statement varchar(850)
      select @Statement = 'declare @cStatement varchar(255)
      declare G_cursor CURSOR for
      select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
      then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
      else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
      from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
      where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
      and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
      order by a.TABLE_NAME
      set nocount on
      OPEN G_cursor
      FETCH NEXT FROM G_cursor INTO @cStatement
      WHILE (@@FETCH_STATUS <> -1)
      begin
      exec (@cStatement)
      FETCH NEXT FROM G_cursor INTO @cStatement
      end
      close G_cursor
      DEALLOCATE G_cursor
      set nocount off'
      from SY00100
      exec (@Statement)
      end
      else begin
      declare @cStatement varchar(255)
      declare G_cursor CURSOR for
      select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
      then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
      else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
      from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
      where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
      order by a.TABLE_NAME
      set nocount on
      OPEN G_cursor
      FETCH NEXT FROM G_cursor INTO @cStatement
      WHILE (@@FETCH_STATUS <> -1)
      begin
      exec (@cStatement)
      FETCH NEXT FROM G_cursor INTO @cStatement
      end
      close G_cursor
      DEALLOCATE G_cursor
      set nocount off
      End

      Then after running that, execute this against Company B on the new server:
      EXEC sp_changedbowner 'DYNSA'

      There will also be additional steps.  Because you have copied data from a different server or Dynamics instance, the user permissions are probably different. You will probably have to make some additional changes to security, I.e. Manually dropping and re-adding the users to Company B.   What I would recommend is that you drop all users from Company B in SQL and then re-add them in the GP user interface.  In SQL you will want to switch to CompanyB and then execute the following
      EXEC sp_dropuser 'Username'
      Run that for each user that is listed in the Company B database, replace Username with their SQL login ID.  

      Note, if you are not fully comfortable with SQL I would highly recommend engaging your GP partner or Microsoft support to assist in moving Company B to DYNAMICS A.  (or vice versa) 

      Rob Klaproth

      Sr. Consultant II – Dynamics GP

      rob.klaproth@amllp.com

       

      ArmaninoLLP

      11512 El Camino Real Suite 311 | San Diego, CA 92130

      858 794 9401 main |  858 207 5137 direct | 619 243 4939  mobile

      LinkedIn | amllp.com


      ——Original Message——

      Hi,

      I want to Merge two Dynamics into one dynamics but not companies..

      for.e.g.

      One Dynamics with Company A and Second Dynamics With Company B,

      after merging One Dynamics with Company A and Company B.

      Let me know what steps and Pro or Cons i need to consider..

      Regards..

      Naveed Ali

      ——————————
      Naveed Ali
      Technical Lead
      Evincible Solutions LLC
      Houstan TX
      8325866423
      naveedali.dewa@gmail.com
      ——————————

    • Beat Bucher

      Member

      January 27, 2017 at 10:02 AM

      Absolutly best suggestion by Rob..
      About your User security, if you have the GP Powertools, the Database Validation function will do all the dirty work for you by fixing the inconsistancies in the SQL security. (it will fix the SQL security based on the GP company access defined for the users).

      ——————————
      Beat Bucher
      Business Analyst, Dynamics GP MVP
      Ultra-Electronics Forensic Technology Inc.
      Montreal QC/Canada
      +1-514-489-4267
      @GP_Beat http://dyngpbeat.wordpress.com/
      Montreal QC GPUG Chapter Leader
      GP2013R2 / MR2012 CU14
      ——————————
      ——————————————-

    • Windi Epperson

      Member

      January 27, 2017 at 10:23 AM

      One other point to add – you need to make sure that both instances of GP are on the same version and have the same modules and ISVs installed before moving the databases.  This may mean that you have to remove modules or ISV products in the company you are going to move.  Some ISV products create extra tables in the GP database, and for some products that isn’t a problem, but for others you need to remove the extra tables to ensure there are no conflicts or issues.  Most ISVs will give you a set of instructions on how to remove their products.
      Thanks
      Windi

      ——————————
      Windi Epperson
      President/GP Senior Consultant
      Advanced Integrators, Inc.
      Norman OK
      405-946-1774 Ext 102
      ——————————
      ——————————————-

    • Bruce Strom

      Member

      January 27, 2017 at 10:37 AM

      Do you really want to merge two companies?
      Practically that is undoable.

      What is doable is merging two general ledgers, and also two sets of payables/receviables data, if the master record numbers and GL accounts do not conflict in the two companies.

      This can be done in SQL.  If you do this, will need to keep in mind there are probably conflicts in the account index numbers for the GL chart of accounts.  The easiest way to fix this is to simply take the largest account index number of  the largest GL company, and then add the account index numbers of the second company to that number, so that is also unique.

      ——————————
      Bruce Strom
      Programmer
      Associated Grocers of Florida
      sunrise FL
      ——————————
      ——————————————-

    • Rob Klaproth

      Member

      January 27, 2017 at 3:51 PM

      Yes, I did forget to mention both systems need to be on EXACTLY the same version of GP (SP Level).
      You will want to check this by going to DYNAMICS DB on both systems and running this in SQL Management Studio:
      Select * from dynamics..db_upgrade where prodid = 0
      Verify that the db versions returned match exactly – if they don’t you will need to apply an SP to the system that is behind to get it on the same level.
      On that same note, you will want to verify all the modules are the same,
      SELECT DISTINCT PRODID FROM DYNAMICS..DB_UPGRADE WHERE INTERID = ‘<Type in SQL DB Name here>’ (THE INTERID is the up-to 5 letter ID of the database, I.e. (ZCOMP)
      This will return all the product id’s installed, you’ll run this against BOTH dynamics databases.  If there is a mis-match you may need to load a product, I.e. Fixed assets or project accounting on the system that is missing it, unless your intention is to never use that module.  You can find the list of modules here, the PRODID returned from the query should correspond with these products here: 

      ——Original Message——

      Absolutly best suggestion by Rob..
      About your User security, if you have the GP Powertools, the Database Validation function will do all the dirty work for you by fixing the inconsistancies in the SQL security. (it will fix the SQL security based on the GP company access defined for the users).

      ——————————
      Beat Bucher
      Business Analyst, Dynamics GP MVP
      Ultra-Electronics Forensic Technology Inc.
      Montreal QC/Canada
      +1-514-489-4267
      @GP_Beat http://dyngpbeat.wordpress.com/
      Montreal QC GPUG Chapter Leader
      GP2013R2 / MR2012 CU14
      ——————————

    • Neil MacDonald

      Member

      January 30, 2017 at 9:04 AM

      CRG’s Company Combiner will merge the two companies, then load Binary Stream’s Multi Entity Manager to retain two companies in a single GP database. See our website or contact me for more information.

      ——————————
      Neil MacDonald
      Binary Stream
      Moncton NB
      ——————————
      ——————————————-

    • Lucas Miller

      Member

      January 30, 2017 at 5:02 PM

      Just a couple things to add to the discussion.

      First, make sure that your account frameworks are compatible between the two environments.  If they are not exactly the same you will likely run into issues during an upgrade somewhere down the road, if not in the application before then.

      Second, more immediately you will see issues with your note indexes if you go the route of restoring a backup of Company B over the top of a new database under DYNAMICS A.  When the new company is created the NOTEINDX field in the Company Master (SY01500) will almost surely be less for this new database than it was in the DYNAMICS database it came from.  This means that as you create new records in this company there will be duplicate note indexes.  The recommendation here would be to set the NOTEINDX field for this new company to something higher than what it was in the original DYNAMICS database.

      ——————————
      Lucas Miller
      Support Escalation Engineer
      Microsoft
      Fargo, ND
      ——————————
      ——————————————-

    DSC Communities replied 7 years, 5 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Merge two Dynamics into one dynamics but not companies’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!