[NaveedAli Ali]

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]

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]

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]

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]

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]

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: 

https://dynamicsgp.wordpress.com/2010/11/30/dynamics-set-list-of-dynamics-gp-products/

——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]

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]

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
——————————
——————————————-